topic Re: 13 CYCLE MONTHS with in A YEAR in QlikView

13 CYCLE MONTHS with in A YEAR

kakani87 — Thu, 28 Dec 2017 08:25:58 GMT

Dear All,

I'm having a scenario to display 13 cycle months based on the dates given like 1/4/2017 to 31/4/2017

Now i'm clear till this but my question is how to display the missing dates for each cycle with given nickname

available data

Date | Cycle Month | Nickname

01-04-2017 CYCLE MONTH 01 UNIFY

30-04-2017 CYCLE MONTH 02 POLAR

28-05-2017 CYCLE MONTH 03 ACTIVE

25-06-2017 CYCLE MONTH 04 DEFINE

23-07-2017 CYCLE MONTH 05 EMPOWER

20-08-2017 CYCLE MONTH 06 ORGANISE

17-09-2017 CYCLE MONTH 07 CHANNEL

15-10-2017 CYCLE MONTH 08 HARMONY

12-11-2017 CYCLE MONTH 09 PULSE

10-12-2017 CYCLE MONTH 10 PERFECT

07-01-2018 CYCLE MONTH 11 DISSOLVE

04-02-2018 CYCLE MONTH 12 DEDICATE

04-03-2018 CYCLE MONTH 13 COSMIC

my requirement is something like this

Date | Cycle Month | Nickname

01-04-2017 CYCLE MONTH 01 UNIFY

02-04-2017 CYCLE MONTH 01 UNIFY

03-04-2017 CYCLE MONTH 01 UNIFY

29-04-2017 CYCLE MONTH 01 UNIFY

30-04-2017 CYCLE MONTH 02 POLAR

01-05-2017 CYCLE MONTH 02 POLAR

02-05-2017 CYCLE MONTH 02 POLAR

27-05-2017 CYCLE MONTH 02 POLAR

28-05-2017 CYCLE MONTH 03 ACTIVE

29-05-2107 CYCLE MONTH 03 ACTIVE

24-06-2017 CYCLE MONTH 03 ACTIVE

25-06-2017 CYCLE MONTH 04 DEFINE

i want to display the records in this way because this dates should be associated with normal 12 months calendar and then i need to build a report for cycle month

Kindly find the attached sample Qvw and do the needful.

Regards,

Kakani.

Re: 13 CYCLE MONTHS with in A YEAR

sunny_talwar — Thu, 28 Dec 2017 09:05:17 GMT

Try this

Cycle_Month:

LOAD c_code,

cycle_name,

Date,

nick_name

FROM

cycle_month.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTable:

LOAD *,

Alt(Peek('Date') - 1, Today()) as End_Date

Resident Cycle_Month

Order By Date desc;

FinalTable:

NoConcatenate

LOAD c_code,

cycle_name,

Date(Date + IterNo() - 1) as Date,

nick_name

Resident NewTable

While Date + IterNo() - 1 <= End_Date;

DROP Table Cycle_Month, NewTable;

Re: 13 CYCLE MONTHS with in A YEAR

kakani87 — Thu, 28 Dec 2017 09:18:24 GMT

Thank you very much Sunny for the response.

Re: 13 CYCLE MONTHS with in A YEAR

kakani87 — Thu, 28 Dec 2017 09:44:10 GMT

Hi Sunny,

Suppose to be 13 cycle months as per my requirement but here it is displaying only 12 months could you check this once

Re: 13 CYCLE MONTHS with in A YEAR

sunny_talwar — Thu, 28 Dec 2017 12:16:13 GMT

But based on the output you asked for, I am not sure where does 13 month comes from? Would you be able to provide the expected output date range more clearly?