Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Help with dates??

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

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

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

View solution in original post

7 Replies
Highlighted
Master II
Master II

What is the logic to create output table?

Highlighted
Creator III
Creator III

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

Highlighted
Master II
Master II

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;

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

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

Highlighted
Specialist
Specialist

What is the logic for Total ID??

Highlighted
Specialist
Specialist

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

View solution in original post