topic Re: Help with dates?? in QlikView

Help with dates??

qlikuser222 — Tue, 21 Mar 2017 00:55:57 GMT

Hi All,

I need help with calculations based on dates.

I have following data

ID Status RenewDT CancelDT

1 A 2016/03

2 A 2016/06

3 A 2017/01

4 A 2017/01

5 A 2017/02

6 C 2017/01

7 C 2017/01

8 C 2017/02

I want to produce below table. I am unable to produce the below table with the above data.

Period Total ID Renewed Cancel

2017/01 3 2 2

2017/02 2 1 1

2017/03 0 0 0

2017/04 0 0 0

2017/05 0 0 0

2017/06 0 0 0

2017/07 0 0 0

2017/08 0 0 0

2017/09 0 0 0

2017/10 0 0 0

2017/11 0 0 0

2017/12 0 0 0

Please help!!

Thanks

Re: Help with dates??

sushil353 — Tue, 21 Mar 2017 04:52:58 GMT

What is the logic to create output table?

Re: Help with dates??

hemanthaanichet — Tue, 21 Mar 2017 05:05:24 GMT

Hi Nic,

As per my understand i solved it some what let me know if any queries u have and provide the sample data too

find the attachment

Re: Help with dates??

sushil353 — Tue, 21 Mar 2017 05:13:23 GMT

Try this code:

Temp:

LOAD * Inline

[

ID,Status,RenewDT,CancelDT

1,A,2016/03,

2,A,2016/06,

3,A,2017/01,

4,A,2017/01,

5,A,2017/02,

6,C, ,2017/01

7,C, ,2017/01

8,C, ,2017/02

];

NoConcatenate

Temp2:

LOAD

ID,Status,RenewDT as Period,1 as DTStatus

Resident Temp

where len(RenewDT)>0;

LOAD

ID,Status,CancelDT as Period,2 as DTStatus

Resident Temp

where len(CancelDT)>0;

Drop Table Temp;

stage1:

LOAD count(ID) as Renewed

,Period

Resident Temp2

where Status = 'A'

Group by Period;

Left Join

LOAD count(ID) as Cancel

,Period

Resident Temp2

where Status = 'C'

Group by Period;

Left Join

LOAD count(ID) as TotalID

,Period

Resident Temp2

Group by Period;

DROP Table Temp2;

Re: Help with dates??

qlikuser222 — Tue, 21 Mar 2017 14:47:53 GMT

Thanks a lot,

But I want the output table like below

Period Total ID Renewed Cancel

2017/01 3 2 2

2017/02 2 1 1

2017/03 0 0 0

2017/04 0 0 0

2017/05 0 0 0

2017/06 0 0 0

2017/07 0 0 0

2017/08 0 0 0

2017/09 0 0 0

2017/10 0 0 0

2017/11 0 0 0

2017/12 0 0 0

Re: Help with dates??

qlikuser222 — Tue, 21 Mar 2017 15:02:09 GMT

Thanks a lot,

But I want the output table like below

Period Total ID Renewed Cancel

2017/01 3 2 2

2017/02 2 1 1

2017/03 0 0 0

2017/04 0 0 0

2017/05 0 0 0

2017/06 0 0 0

2017/07 0 0 0

2017/08 0 0 0

2017/09 0 0 0

2017/10 0 0 0

2017/11 0 0 0

2017/12 0 0 0

Re: Help with dates??

Anonymous — Tue, 21 Mar 2017 15:15:55 GMT

What is the logic for Total ID??

Re: Help with dates??

Anonymous — Tue, 21 Mar 2017 15:29:01 GMT

Assuming the TotalID for 2017/01 is 4. Attached is your output.