Text column in the Expressions

[#1]

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

[Colin-Albert]

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]

[its_anandrjs]

If you have small sample file then provide please it will be more easy.

[its_anandrjs]

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])

[agilos_mla · Accepted solution]

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

[maxgro]

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;