Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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
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.
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;
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
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.
Hi Jagan,
I tried this one but not getting proper O/P. PFA the app,Excel file and expected O/P.
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.
It's attached now.
Hi,
What is your expected output?
Regards,
jagan.