topic Re: Counter in Script in QlikView

Counter in Script

Tue, 07 Apr 2015 07:29:53 GMT

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.

Re: Counter in Script

datanibbler — Tue, 07 Apr 2015 07:38:42 GMT

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

Re: Counter in Script

jagan — Tue, 07 Apr 2015 07:50:44 GMT

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.

Re: Counter in Script

pokassov — Tue, 07 Apr 2015 07:53:51 GMT

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;

Re: Counter in Script

Tue, 07 Apr 2015 07:55:36 GMT

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

Re: Counter in Script

jagan — Tue, 07 Apr 2015 08:00:32 GMT

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.

Re: Counter in Script

Tue, 07 Apr 2015 09:07:22 GMT

Hi Jagan,

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

Re: Counter in Script

jagan — Tue, 07 Apr 2015 09:22:11 GMT

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.

Re: Counter in Script

Tue, 07 Apr 2015 09:40:13 GMT

It's attached now.

Re: Counter in Script

jagan — Tue, 07 Apr 2015 09:43:41 GMT

Hi,

What is your expected output?

Regards,

jagan.

Re: Counter in Script

Tue, 07 Apr 2015 09:50:45 GMT

Thanks Buddy it worked 🙂

Re: Counter in Script

Tue, 07 Apr 2015 09:53:51 GMT

Hey Sorry Buddy for incomplete post, next time will keep in mind :-). Thanks for time

Re: Counter in Script

anbu1984 — Tue, 07 Apr 2015 10:02:35 GMT

Temp:
LOAD Month, 
     Year, 
     Sales, 
     Division
FROM
Data.xlsx
(ooxml, embedded labels, table is Sheet1);


Final:
NoConcatenate
Load *,AutoNumber(Year & Month) Resident Temp Order by Year, Month, Division;


Drop Table Temp;