Repeated count in items

[paulwalker]

HI Community,

I have one scenario, can you please help me 

I have to compare data, which items are repeated on above month..

Suppose, Jan count should be zero, because we don't have previous month for Jan

Feb month count 3, because 3 items were repeated in Feb compare with previous month - Jan

Mar month count 1, because 1 item repeated in Mar compare with previous month - Feb

Load * inline [
Items, Month
1, Jan
2, Jan
7, Jan
3, Jan
8, Jan
3, Feb
4, Feb
5, Feb
1, Feb
2, Feb
7, Mar
4, Mar
];

Output should be:

jan	Feb	Mar
0	3	1

Thanks in Advance

[chaorenzhu]

convert your Month to numerical format, order by Items, then use previous() to see if the difference is 1

to duplicate your desired output, some transformation is needed:

table1:
Load * inline [
Items, Month
1, Jan
2, Jan
7, Jan
3, Jan
8, Jan
3, Feb
4, Feb
5, Feb
1, Feb
2, Feb
7, Mar
4, Mar
];

table2:
load Items,Month(Date#(Month, 'MMM')) as Month,
if(Num(Month(Date#(Month, 'MMM')))-previous(Num(Month(Date#(Month, 'MMM'))))=1,1,0) as repeat_flag
resident table1 order by Items;

table3:
load Month,count(Items) as repeat_count resident table2 where repeat_flag=1 group by Month;
load Month,0 as repeat_count resident table2 where repeat_flag=0;

table4:
load Month,max(repeat_count) as repeat_count2 resident table3 group by Month;

table5: generic load 1 as field,Month,repeat_count2 resident table4;

drop tables table1,table2,table3,table4;

[paulwalker]

sorry for the delay response..

Can't we write in Set expression ?

[chaorenzhu]

of course you can, that would also be easier. but it depends on how you want your output to be. If you just need a table with a column called Month, with Jan, Feb and Mar being one row each and another column as the value of repeated count, then you don't need this generic load to transform table from long to wide