Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have one issue while creating the pivot table. I have a column in the table which is a text column it is having both numbers and the text value. How to define this as a expressions in pivot table
e.g.
Date | Reason | Repsonse |
---|---|---|
01-jan-2014 | OUTLET STOCK | 30 |
01-Jan-2014 | STOCK EXPIRY | 31-JAN-2014 |
02-JAN-2014 | OUTLET STOCK | 40 |
02-JAN-2014 | STOCK EXPIRY | 28-FEB-2014 |
03-JAN-2014 | OUTLET STOCK | 50 |
03-JAN-2014 | STOCK EXPIRY | 28-FEB-2014 |
I need a report like this
Date | OUTLET STOCK | STOCK EXPIRY |
---|---|---|
01-jan-2014 | 30 | 31-jan-2014 |
02-jan-2014 | 40 | 28-feb-2014 |
03-jan-2014 | 50 | 28-feb-2014 |
Thanks in advance
Arun
Hi,
I'd suggest to use generic load. This is the standard way to process table that are structured 1 ligne, 1 fact as your example. Here is a link to HIC post:
http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic
Hope it helps,
Michael
If the reason column just has two values, then use an if statement in your load script to separate the data into different fields. If there are many different values in the reason column then look at the GENERIC load function.
Load
Date,
If( reason = 'OUTLET STOCK', RESPONSE) as [OUTLET STOCK],
If( reason = 'EXPIRY DATE', RESPONSE) as [EXPIRY DATE]
If you have small sample file then provide please it will be more easy.
Load your table like below
Temp:
LOAD * INLINE
[
Date, Reason, Repsonse
01-jan-2014, OUTLET STOCK, 30
01-Jan-2014, STOCK EXPIRY, 31-JAN-2014
02-JAN-2014, OUTLET STOCK, 40
02-JAN-2014, STOCK EXPIRY, 28-FEB-2014
03-JAN-2014, OUTLET STOCK, 50
03-JAN-2014, STOCK EXPIRY, 28-FEB-2014
];
LOAD
Date#(Date,'DD-MMM-YYYY') AS Date,
Repsonse as [STOCK EXPIRY]
Resident Temp Where Reason = 'STOCK EXPIRY';
Left Join
LOAD
Date#(Date,'DD-MMM-YYYY') AS Date,
Repsonse AS [OUTLET STOCK]
Resident Temp Where Reason = 'OUTLET STOCK';
DROP Table Temp;
and in pivot table take dimension and expression like
Dimension:- Date
Dimension:- STOCK EXPIRY
Expression:- sum([OUTLET STOCK])
Hi,
I'd suggest to use generic load. This is the standard way to process table that are structured 1 ligne, 1 fact as your example. Here is a link to HIC post:
http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic
Hope it helps,
Michael
Temp:
LOAD * INLINE
[
Date, Reason, Repsonse
01-jan-2014, OUTLET STOCK, 30
01-Jan-2014, STOCK EXPIRY, 31-JAN-2014
02-JAN-2014, OUTLET STOCK, 40
02-JAN-2014, STOCK EXPIRY, 28-FEB-2014
03-JAN-2014, OUTLET STOCK, 50
03-JAN-2014, STOCK EXPIRY, 28-FEB-2014
];
Table:
load
Date(Date#(Date,'DD-MMM-YYYY'),'DD-MMM-YYYY') AS Date,
MaxString(if(wildmatch(Reason, 'OUTLET*'), Repsonse)) as [OUTLET STOCK],
date(Date#(MaxString(if(wildmatch(Reason, 'STOCK*'), Repsonse)),'DD-MMM-YYYY'),'DD-MMM-YYYY') as [STOCK EXPIRY]
Resident Temp
group by Date(Date#(Date,'DD-MMM-YYYY'),'DD-MMM-YYYY');
DROP Table Temp;