topic Re: Pivot Table in QlikView

Pivot Table

soha1902 — Mon, 15 Dec 2014 07:23:55 GMT

Hi All,

I have following Data:

ProdID	Product	Activated	Deactivated
1	Prod A	02/15/13
2	Prod B	08/05/13
3	Prod C	01/07/14
1	Prod A		02/10/14
2	Prod B		02/26/14
1	Prod A	11/15/14

Now my requirement is, if any Product is active then its price will be display as 10 and if it is deactive then its price will be 0.

For example Prod A is activated on 15 feb 2013 and deactivated on 10 Feb 2014 so from 15th Feb 2013 till 10Feb 2014 its price will be 10. On 10th Feb 2014 Prod A was deactivated and on 15 Nov2014 again got activated so from 10th Feb 2014 to 15 Nov 2014 its price will be 0.

I need to add Price column as a expression and Week column should be pivoted.

Can any one suggest me, how to implement this.

The resultant table shold look like this for Product A

ProdID

Product

Activated

Deactivated

2/18/2014

2/25/2014

-------------'

2/7/2014

2/14/2014

2/21/2014

-------------'

11/14/2014

11/21/2014

till cureent date

Prod A

02/15/13

Prod B

08/05/13

Prod C

01/07/14

Prod A

02/10/14

Prod B

02/26/14

Prod A

11/15/14

Thanks in advance.

Re: Pivot Table

soha1902 — Mon, 15 Dec 2014 09:38:01 GMT

any suggestions please.....

Re: Pivot Table

saumyashah90 — Mon, 15 Dec 2014 09:44:50 GMT

What about products which doesnt have deactivation date... for example Prod C?

Re: Pivot Table

soha1902 — Mon, 15 Dec 2014 09:48:48 GMT

it will always show Price =10 because it never got deactivated.....

Re: Pivot Table

saumyashah90 — Mon, 15 Dec 2014 09:54:33 GMT

Also the Dates shown above should be what?....i mean what should be the period of the dates in the horizontal line?

Re: Pivot Table

soha1902 — Mon, 15 Dec 2014 10:00:16 GMT

Those dates are weekly dates.

Re: Pivot Table

lironbaram — Mon, 15 Dec 2014 10:02:19 GMT

hi have a look at the attach example

have a look at the script where most of the work is done

Re: Pivot Table

Mon, 15 Dec 2014 12:32:26 GMT

I know it's maybe a stupid question, but I can see from your example that Prod A (ID=1) has two activation date...

I think you should add an expression: if=(num(Today())>num(Exp.date),0,10). Then add date as a dimension. I suppose, data is present in the DB from activation date, therefore you shouldn't have a date renge exceeding the range of the to be analysed dates.

Br,

Re: Pivot Table

soha1902 — Tue, 16 Dec 2014 08:35:55 GMT

Hi Liron,

Thanks for your help. It showing correct values for Product A and Product C but not for Product B. Please see below image:

For Product B it is showing Deactivated date is current date but it excel it was 02/26/2014. see below:

I attached both qvw and excel file in attachment. Please help to solve this.

Re: Pivot Table

lironbaram — Tue, 16 Dec 2014 10:23:46 GMT

fix it

have a look

Re: Pivot Table

soha1902 — Tue, 16 Dec 2014 10:42:29 GMT

Thanks a lot liron....

Re: Pivot Table

soha1902 — Mon, 09 Feb 2015 11:28:05 GMT

Hi Liron,

I have one requirement....if you have some time can you please suggest me abt following:

http://community.qlik.com/thread/151216

Thanks.