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: 
Not applicable

Counter in Script

I have Year and Month in script and want to add one more column in table which has sequential increment whenever month or year increase . Please suggest the logic to do this.

1 Solution

Accepted Solutions
pokassov
Specialist
Specialist

Hi!

T1:

load

     *,

     if(Previous(Year)<>Year or Previous(Month)<>Month, rangesum(1, peek('Flag')),peek(Flag)) as Flag

resident calendar

order by Year, Month, Data;

drop table Calendar;

rename Table t1 to Calendar;

View solution in original post

12 Replies
datanibbler
Champion
Champion

Hi Navdeep,

then you have to first sort your table by Year and Month and then use the PEEK() or PREVIOUS() function to look at the record right above the one you're currently loading and to see if it's the same - and if it's not, increase a counter, otherwise copy the counter_value from above, again using one of those functions.

HTH

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

LOAD

*,

If(Peek(Year) <> Year OR Peek(Month) <> Month, 1, Peek(Sequence) + 1) AS Sequence

If(

FROM DataSource

ORDER BY Year, Month;

Hope it helps you.

Regards,

Jagan.

pokassov
Specialist
Specialist

Hi!

T1:

load

     *,

     if(Previous(Year)<>Year or Previous(Month)<>Month, rangesum(1, peek('Flag')),peek(Flag)) as Flag

resident calendar

order by Year, Month, Data;

drop table Calendar;

rename Table t1 to Calendar;

Not applicable
Author

Seems good, can you give the syntax as I am using

If((Month)<>Peek(Month) or Year<>Peek(Year), Peek(Check)+1, Peek(Check)) as Check1

and not getting the result.

Regards,

Navdeep

jagan
Luminary Alumni
Luminary Alumni

Hi Navdeep,

Did you checked this.

LOAD

*,

If(Peek(Year) <> Year OR Peek(Month) <> Month, 1, Peek(Sequence) + 1) AS Sequence

If(

FROM DataSource

ORDER BY Year, Month;

Regards,

Jagan.

Not applicable
Author

Hi Jagan,

I tried this one but not getting proper O/P. PFA the app,Excel file and expected O/P.

jagan
Luminary Alumni
Luminary Alumni

Hi Navdeep,

How can I test this without the excel file?  Before creating post in community attach sample file and data then it would be easier to give solution.

Regards,

Jagan.

Not applicable
Author

It's attached now.

jagan
Luminary Alumni
Luminary Alumni

Hi,

What is your expected output?

Regards,

jagan.