Qlikview Notes

1.
[Raw Data]:
LOAD *
FROM
[https://community.qlik.com/thread/228627]
(html, codepage is 1252, embedded labels, table is @1);

Concatenate([Raw Data])
LOAD *
FROM
[https://community.qlik.com/thread/228627]
(html, codepage is 1252, embedded labels, table is @2);

NoConcatenate
Data:
LOAD
if(isnull(Code),’Code Missing’,Code) as Code,
if(isnull(emp),’Emp Missing’,emp) as emp,
if(isnull(sale),0,sale) as sale,
if(isnull(qty),0,qty) as qty,
if(isnull([sale type]),’Sale Type Missing’,[sale type]) as [sale type],
if(isnull(model),’Model Missing’,model) as model,
if(isnull(Region),’Region Missing’,Region) as Region,
if(isnull(country),’Country Missing’,country) as country,
if(isnull(state),’State Missing’,state) as state,
if(isnull(reporting),’Reporting Missing’,reporting) as reporting,
if(isnull(Month),’Month Missing’,Month) as Month
Resident [Raw Data];

Drop Table [Raw Data];

LinkTable:
load *, Pick(Match(MonthName, ‘Jan’, ‘Feb’, ‘Mar’, ‘Apr’, ‘May’, ‘Jun’, ‘Jul’, ‘Aug’, ‘Sep’, ‘Oct’, ‘Nov’, ‘Dec’),
’01’, ’02’, ’03’, ’04’, ’05’, ’06’, ’07’, ’08’, ’09’, ’10’, ’11’, ’12’) as Test;
if(Dimensionality()=1 and sum(Sales)<150,RGB(151,255,255),if(Dimensionality()=2 and sum(Sales)<=20,RGB(0,238,0),if(Dimensionality()=3 and Sum(Sales)<=20,RGB(255,130,171))))
rangesum(above(sum(Amount),0,rowno()))
Time(SubField(Class(Time,1/144),’ <= x < ‘, 1)) as StartTime;

If( ShippedDate – RequiredDate <= -5, ‘Too early’,
If( ShippedDate – RequiredDate <= 0, ‘Just in time’,
If( ShippedDate – RequiredDate <= 5, ‘Small delay’,
‘Large delay’ ))) as Delay,

Or if you use dual values:

If( ShippedDate – RequiredDate <= -5, Dual( ‘Too early’, -5 ),
If( ShippedDate – RequiredDate <= 0, Dual( ‘Just in time’, 0 ),
If( ShippedDate – RequiredDate <= 5, Dual( ‘Small delay’, 5 ),
Dual( ‘Large delay’, 10 )))) as Delay,
Round( ShippedDate – RequiredDate , 5 ) as Delay,
Class( ShippedDate – RequiredDate , 5 ) as Delay,

LET vTrendStart = ‘=Date(YearStart(Addmonths(Max(MonthYear), -12)), ‘ & chr(39) & ‘MMMYY’ & chr(39) & ‘)’;
LET vTrendEnd = ‘=Date(MonthStart(Max({<_History = {1}>} MonthYear)), ‘ & chr(39) & ‘MMMYY’ & chr(39) & ‘)’;

Date(MonthStart(SERVICE_START_TIME), ‘MMM YYYY’) AS Month,
Date(Floor(SERVICE_START_TIME)) AS Date,
Hour(SERVICE_START_TIME) AS Hour,
Time( SERVICE_START_TIME) as Time,
SERVICE_START_TIME as TimeStamp,

Dual(Time(SubField(Class(Time,1/144),’ <= x < ‘, 1))&’ – ‘&Time(SubField(Class(Time,1/144),’ <= x < ‘, 2)), Class(Time,1/144)) as [10 Min Interval],
Time(SubField(Class(Time,1/144),’ <= x < ‘, 1)) as StartTime;

fabs( -3.8 ) returns 3.8
If(Previous(DKey) = DKey, RangeSum(Peek(IntRank), 1), 1) AS IntRank
timestamp(timestamp#(date(Date,’DD-MMM-YY’) & ‘ ‘ & Hour & ‘:00:00′,’DD-MMM-YY hh:mm:ss’),’DD-MMM-YY hh:mm:ss’) as DateHour;

Wildmatch can test against multiple values:

wildMatch(text, ‘*error*’, ‘*warning*’)

The match() functions return a number indicating which of the comparison strings was found. You can use this index number nested in a pick function to do “wildcard mapping” as an alternative to a nested if() function.

pick(
wildmatch(PartNo,
‘*99’, ‘P1586’, ‘?15*’, ‘?17*’, ‘*’
),
‘Taxes’, ‘Premium Fuel’, ‘Fuel’, ‘Lubricant’, ‘Other’)
We’ll use a Text Format expression of:
=if(Commission > 200, ‘<B>’)

When used in script, Now(0) returns the end time of the previous script execution, not the time of the current script execution.

Now(1) always returns the time when the function is executed — the “current” time.To Summarize, use Now(1) in script, Now(0) in charts.

Sum(NULL) is zero.
So, the bottom line is that NULLs are sometimes visible, sometimes not. Sometimes they are displayed as dashes, and when aggregated, usually as zeros. But they are never selectable.

Qualify *;
Unqualify Key_Field1, Key_Field2;

If( Peek( Month(Date) )=Month(Date), Peek( Rate ), Rate ) as Rate
If( IsNull( Field ), Peek( Field ), Field ) as Field

TempTable_Data:
Load Product, Date, Amount
From DataTable ;
Data:
Load Product, Date, Amount,
If( Product=Peek(Product), // if the Product is the same as in the previous row
RangeSum(Amount,Peek(AccumulatedAmount)),
RangeSum(Amount)) as AccumulatedAmount
Resident TempTable_Data
Order By Product, Date ; // order by Product and by Date within the Product
Drop Table TempTable_Data ;
Date(Floor(Timestamp)) as Date
Time(Floor(Frac(Timestamp),1/24/60),’hh:mm’) as Time

The 32-bit version of QlikView will only work with the 32-bit ODBC drivers, however the 64-bit version will work with both 32-bit and 64-bit ODBC drivers.
Date(Floor(Timestamp)) as Date,
Time(Floor(Frac(Timestamp),1/24/60)) as Time,

The wizard can only handle Excel files.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.