topic Re: Quarter counter in LOAD statement in QlikView

Quarter counter in LOAD statement

summerrain — Mon, 04 Jun 2018 13:26:20 GMT

Hi,

I have this type of table in data model:

Period	Quarter	PeriodCounter
201701	2017-Q1	1
201702	2017-Q1	2
201703	2017-Q1	3
201704	2017-Q2	4
201705	2017-Q2	5
201706	2017-Q2	6
201707	2017-Q3	7
201708	2017-Q3	8
201709	2017-Q3	9
201710	2017-Q4	10
201711	2017-Q4	11
201712	2017-Q4	12
201801	2018-Q1	13

I load recno() as PeriodCounter. I would like to add another field QuarterCounter, but have no clue how to achieve that?

Load

Period,

Quarter,

recno() as PeriodCounter,

??? as QuarterCounter

Desired result:

Period	Quarter	PeriodCounter	QuarterCounter
201701	2017-Q1	1	1
201702	2017-Q1	2	1
201703	2017-Q1	3	1
201704	2017-Q2	4	2
201705	2017-Q2	5	2
201706	2017-Q2	6	2
201707	2017-Q3	7	3
201708	2017-Q3	8	3
201709	2017-Q3	9	3
201710	2017-Q4	10	4
201711	2017-Q4	11	4
201712	2017-Q4	12	4
201801	2018-Q1	13	5

Your help is greatly appreciated!

Re: Quarter counter in LOAD statement

Anil_Babu_Samineni — Mon, 04 Jun 2018 13:30:37 GMT

Try this?

Count(TOTAL <Quarterr> PeriodCounter)

If it is in script,

Table:

Load

Period,

Quarter,

recno() as PeriodCounter From Table;

Left Join (Table)

Load Quarter, Count(PeriodCounter) as QuarterCounter Group By Quarter Resident Table;

Re: Quarter counter in LOAD statement

sunny_talwar — Mon, 04 Jun 2018 13:30:42 GMT

Two ways you can do this

1) AutoNumber(Quarter) as QuarterCounter

2) If(Quarter = Previous(Quarter), Peek('QuarterCounter'), RangeSum(Peek('QuarterCounter'), 1)) as QuarterCounter

For the second method to work without any flaws, make sure to sort the table in ascending order of Period

Re: Quarter counter in LOAD statement

summerrain — Mon, 04 Jun 2018 13:34:12 GMT

Thanks Anil!

Re: Quarter counter in LOAD statement

summerrain — Mon, 04 Jun 2018 13:35:05 GMT

Thanks Sunny, the first one looks very elegant!

Re: Quarter counter in LOAD statement

sunny_talwar — Mon, 04 Jun 2018 13:45:52 GMT

It might look elegant, but might not give as good of as a performance as the second one. But since we are talking about calendar dimension (quarter), there might not be more than 1000 values in your calendar autonumber might not be too bad