topic Re: Text column in the Expressions in QlikView

Text column in the Expressions

Sun, 20 Apr 2014 09:50:31 GMT

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

Re: Text column in the Expressions

Colin-Albert — Sun, 20 Apr 2014 10:11:45 GMT

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]

Re: Text column in the Expressions

its_anandrjs — Sun, 20 Apr 2014 10:16:17 GMT

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

Re: Text column in the Expressions

its_anandrjs — Sun, 20 Apr 2014 10:27:28 GMT

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

Re: Text column in the Expressions

agilos_mla — Sun, 20 Apr 2014 10:45:01 GMT

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

Re: Text column in the Expressions

maxgro — Sun, 20 Apr 2014 20:19:18 GMT

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;