topic Re: Manipulating the result of intervalMatch() in script in QlikView

Manipulating the result of intervalMatch() in script

Anonymous — Thu, 14 Jul 2016 18:08:16 GMT

Hi all,

I am doing intervalmatch from the post Counting the number of occurrences in between dates but I face a problem.

The intervalmatch is working ok in the application, but I would like to do some calculation with the results. Acctually, I would like to have the same table of results (TABLE_OUT) from previous question in LOAD script. Is that possible?

Re: Manipulating the result of intervalMatch() in script

sunny_talwar — Thu, 14 Jul 2016 18:27:37 GMT

Would you be able to share what you have thus far?

Thanks,

Sunny

Re: Manipulating the result of intervalMatch() in script

Anonymous — Thu, 14 Jul 2016 19:15:17 GMT

share us your QVW file with sample data . And tell us what output you need.

Re: Manipulating the result of intervalMatch() in script

Anonymous — Thu, 14 Jul 2016 19:20:57 GMT

Hi Sunny,

Please, see below what I have done so far.

TABLE_P:

LOAD * INLINE [

ID, REGION, Product, Launch Date, START DATE, END DATE

1, A, pineapple, 10/02/2015, 10/02/2015, 09/02/2020

2, B, pineapple, 10/02/2016, 10/02/2016, 09/02/2022

3, C, pineapple, 12/02/2018, 12/02/2018, 11/02/2020

4, A, apple, 10/02/2013, 10/02/2013, 09/02/2017

5, B, apple, 15/05/2016, 15/05/2016, 14/05/2023

6, C, apple, 16/05/2016, 16/05/2016, 15/05/2017

7, A, pineapple, 05/05/2015, ,

8, B, apple, 25/02/2018, ,

9, C, Mango, 22/05/2017, ,

10, A, Mango, 26/05/2020, ,

];

TABLE_C:

Load REGION as REG_A,

count( DISTINCT ID) as QTY_PROD,

YEAR([Launch Date]) as YR_Launch

resident TABLE_P group by YEAR([Launch Date]), REGION;

//OUTSIDE_COUNT

TABLE_COUNT:

load *,

IF(Previous(REG_A)=REG_A, if(len(peek(QTY_ACC))>0, peek(QTY_ACC), Previous(QTY_PROD)) + QTY_PROD) as QTY_ACC

Resident TABLE_C Order by REG_A, YR_Launch;

drop Table TABLE_C;

LOAD * INLINE [

YEAR_STT, NUMBER

2015, 1

2016, 1

2017, 1

2018, 1

2019, 1

2020, 1

2021, 1

2022, 1

2023, 1

2024, 1

2025, 1

2026, 1

2027, 1

2028, 1

2029, 1

2030, 1

2031, 1

];

//INSIDE_COUNT

TABLE_INTERVAL:

NoConcatenate

load

year([START DATE]) as INTERVAL_START,

year([END DATE]) as INTERVAL_END,

REGION as REG_A,

Resident TABLE_P;

DATA:

IntervalMatch(YEAR_STT) load

INTERVAL_START, INTERVAL_END

Resident TABLE_INTERVAL;

Re: Manipulating the result of intervalMatch() in script

sunny_talwar — Thu, 14 Jul 2016 19:28:38 GMT

INSIDE_COUNT and ALL_COUNT need to be from the script or front end charts?

Re: Manipulating the result of intervalMatch() in script

Anonymous — Thu, 14 Jul 2016 19:50:10 GMT

Do you know how to attach a file. I was struggled in doing that in load script to perform better, but I did in chart expression.

So I would like to share the results.

Re: Manipulating the result of intervalMatch() in script

sunny_talwar — Thu, 14 Jul 2016 20:29:18 GMT

Check here:

Uploading a Sample