Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator III

IntervalMatch until Current Month

Hello,

I have the following table with different ID:

- Each ID has TYPE = Z010 (Meters) and TYPE = Z070 (Sales_%).

- Note that START_DATE and END_DATE have format = DD/MM/YYYY.

- The END_DATE could be 31/12/9999. This means the current VALUE is that one, but it could change in the future. If it changes today, then the value will set to 20/04/2018 and the database will automatically generate a new row with START_DATE = 21/04/2018 to END_DATE = 31/12/9999.

IDTYPEKEYVALUESTART_DATEEND_DATEMEASURE
IM001Z010IM001|Z01034,3601/01/201828/02/2018METERS
IM001Z010IM001|Z01064,2801/03/201831/12/9999METERS
IM001Z070IM001|Z0700,748801/01/201828/02/2018SALES_%
IM001Z070IM001|Z0701,174101/03/201831/12/9999SALES_%
IM002Z010IM002|Z0106001/01/201831/03/2018METERS
IM002Z010IM002|Z01073,2901/04/201831/12/9999METERS
IM002Z070IM002|Z0700,764801/01/201831/03/2018SALES_%
IM002Z070IM002|Z0701,055601/04/201831/12/9999SALES_%
IM003Z010IM003|Z01046,2701/01/201831/03/2018METERS
IM003Z010IM003|Z01093,5801/04/201831/12/9999METERS
IM003Z070IM003|Z0700,651601/01/201831/03/2018SALES_%
IM003Z070IM003|Z0701,405301/04/201831/12/9999SALES_%
IM004Z010IM004|Z010323,5501/01/201809/05/2018METERS
IM004Z010IM004|Z010273,6110/05/201831/12/9999METERS
IM004Z070IM004|Z0701,249401/01/201809/05/2018SALES_%
IM004Z070IM004|Z0701,039310/05/201831/12/9999SALES_%

What I need is to generate a new field PERIOD with the associated VALUE for Meters and Sales_%:


- The maximum PERIOD should be the current month, in this case, 201804.

IDTYPEKEYVALUEMEASUREPERIOD
IM001Z010IM001|Z01034,36METERS201801
IM001Z010IM001|Z01034,36METERS201802
IM001Z010IM001|Z01064,28METERS201803
IM001Z010IM001|Z01064,28METERS201804
IM001Z070IM001|Z0700,7488SALES_%201801
IM001Z070IM001|Z0700,7488SALES_%201802
IM001Z070IM001|Z0701,1741SALES_%201803
IM001Z070IM001|Z0701,1741SALES_%201804
IM002Z010IM002|Z01060METERS201801
IM002Z010IM002|Z01060METERS201802
IM002Z010IM002|Z01060METERS201803
IM002Z010IM002|Z01073,29METERS201804
IM002Z070IM002|Z0700,7648SALES_%201801
IM002Z070IM002|Z0700,7648SALES_%201802
IM002Z070IM002|Z0700,7648SALES_%201803
IM002Z070IM002|Z0701,0556SALES_%201804
IM003Z010IM003|Z01046,27METERS201801
IM003Z010IM003|Z01046,27METERS201802
IM003Z010IM003|Z01046,27METERS201803
IM003Z010IM003|Z01093,58METERS201804
IM003Z070IM003|Z0700,6516SALES_%201801
IM003Z070IM003|Z0700,6516SALES_%201802
IM003Z070IM003|Z0700,6516SALES_%201803
IM003Z070IM003|Z0701,4053SALES_%201804
IM004Z010IM004|Z010323,55METERS201801
IM004Z010IM004|Z010323,55METERS201802
IM004Z010IM004|Z010323,55METERS201803
IM004Z010IM004|Z010323,55METERS201804
IM004Z070IM004|Z0701,2494SALES_%201801
IM004Z070IM004|Z0701,2494SALES_%201802
IM004Z070IM004|Z0701,2494SALES_%201803
IM004Z070IM004|Z0701,2494SALES_%201804

Do you know how could I get that?

Thank you!!!

1 Solution

Accepted Solutions
sunny_talwar

Try this

Table:

LOAD *,

Date(MonthStart(START_DATE, IterNo()-1), 'YYYYMM') as PERIOD

While MonthStart(START_DATE, IterNo()-1) < RangeMin(MonthStart(Today(), 1), MonthStart(END_DATE, 1));

LOAD * INLINE [

    ID, TYPE, KEY, VALUE, START_DATE, END_DATE, MEASURE

    IM001, Z010, IM001|Z010, "34,36", 01/01/2018, 28/02/2018, METERS

    IM001, Z010, IM001|Z010, "64,28", 01/03/2018, 31/12/9999, METERS

    IM001, Z070, IM001|Z070, "0,7488", 01/01/2018, 28/02/2018, SALES_%

    IM001, Z070, IM001|Z070, "1,1741", 01/03/2018, 31/12/9999, SALES_%

    IM002, Z010, IM002|Z010, 60, 01/01/2018, 31/03/2018, METERS

    IM002, Z010, IM002|Z010, "73,29", 01/04/2018, 31/12/9999, METERS

    IM002, Z070, IM002|Z070, "0,7648", 01/01/2018, 31/03/2018, SALES_%

    IM002, Z070, IM002|Z070, "1,0556", 01/04/2018, 31/12/9999, SALES_%

    IM003, Z010, IM003|Z010, "46,27", 01/01/2018, 31/03/2018, METERS

    IM003, Z010, IM003|Z010, "93,58", 01/04/2018, 31/12/9999, METERS

    IM003, Z070, IM003|Z070, "0,6516", 01/01/2018, 31/03/2018, SALES_%

    IM003, Z070, IM003|Z070, "1,4053", 01/04/2018, 31/12/9999, SALES_%

    IM004, Z010, IM004|Z010, "323,55", 01/01/2018, 09/05/2018, METERS

    IM004, Z010, IM004|Z010, "273,61", 10/05/2018, 31/12/9999, METERS

    IM004, Z070, IM004|Z070, "1,2494", 01/01/2018, 09/05/2018, SALES_%

    IM004, Z070, IM004|Z070, "1,0393", 10/05/2018, 31/12/9999, SALES_%

];

Capture.PNG

View solution in original post

2 Replies
sunny_talwar

Try this

Table:

LOAD *,

Date(MonthStart(START_DATE, IterNo()-1), 'YYYYMM') as PERIOD

While MonthStart(START_DATE, IterNo()-1) < RangeMin(MonthStart(Today(), 1), MonthStart(END_DATE, 1));

LOAD * INLINE [

    ID, TYPE, KEY, VALUE, START_DATE, END_DATE, MEASURE

    IM001, Z010, IM001|Z010, "34,36", 01/01/2018, 28/02/2018, METERS

    IM001, Z010, IM001|Z010, "64,28", 01/03/2018, 31/12/9999, METERS

    IM001, Z070, IM001|Z070, "0,7488", 01/01/2018, 28/02/2018, SALES_%

    IM001, Z070, IM001|Z070, "1,1741", 01/03/2018, 31/12/9999, SALES_%

    IM002, Z010, IM002|Z010, 60, 01/01/2018, 31/03/2018, METERS

    IM002, Z010, IM002|Z010, "73,29", 01/04/2018, 31/12/9999, METERS

    IM002, Z070, IM002|Z070, "0,7648", 01/01/2018, 31/03/2018, SALES_%

    IM002, Z070, IM002|Z070, "1,0556", 01/04/2018, 31/12/9999, SALES_%

    IM003, Z010, IM003|Z010, "46,27", 01/01/2018, 31/03/2018, METERS

    IM003, Z010, IM003|Z010, "93,58", 01/04/2018, 31/12/9999, METERS

    IM003, Z070, IM003|Z070, "0,6516", 01/01/2018, 31/03/2018, SALES_%

    IM003, Z070, IM003|Z070, "1,4053", 01/04/2018, 31/12/9999, SALES_%

    IM004, Z010, IM004|Z010, "323,55", 01/01/2018, 09/05/2018, METERS

    IM004, Z010, IM004|Z010, "273,61", 10/05/2018, 31/12/9999, METERS

    IM004, Z070, IM004|Z070, "1,2494", 01/01/2018, 09/05/2018, SALES_%

    IM004, Z070, IM004|Z070, "1,0393", 10/05/2018, 31/12/9999, SALES_%

];

Capture.PNG

microwin88x
Creator III
Creator III
Author

Thank you Sunny! It worked