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: 
summerrain
Contributor III
Contributor III

Quarter counter in LOAD statement

Hi,

I have this type of table in data model:

PeriodQuarterPeriodCounter
2017012017-Q11
2017022017-Q12
2017032017-Q13
2017042017-Q24
2017052017-Q25
2017062017-Q26
2017072017-Q37
2017082017-Q38
2017092017-Q39
2017102017-Q410
2017112017-Q411
2017122017-Q412
2018012018-Q113

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:

PeriodQuarterPeriodCounterQuarterCounter
2017012017-Q111
2017022017-Q121
2017032017-Q131
2017042017-Q242
2017052017-Q252
2017062017-Q262
2017072017-Q373
2017082017-Q383
2017092017-Q393
2017102017-Q4104
2017112017-Q4114
2017122017-Q4124
2018012018-Q1135

Your help is greatly appreciated!

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

5 Replies
Anil_Babu_Samineni

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

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

summerrain
Contributor III
Contributor III
Author

Thanks Anil!

summerrain
Contributor III
Contributor III
Author

Thanks Sunny, the first one looks very elegant!

sunny_talwar

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