Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to make a table in script in which i want the following fields:
MonthYear, Count(New Products)
These new products are the ones which arent present in last month
Eg
MonthYear Product
Aug'17 A
Aug'17 B
Aug'17 C
Sep'17 A
Sep17 D
The count for sep'17 should be 1.
it is not necessary that all products from Aug'17 will flow in Sep'17
Hence we need to compare each product in curr month from prev month.
Can you please suggest how.
I need to implement this in the script
This?
Temp:
Load *,
If(Exists(Product),0,1) as NewFlag
Inline [
MonthYear, Product
Aug'17, A
Aug'17, B
Aug'17, C
Sep'17, A
Sep'17, D
Nov'17, E
Nov'17, M
Dec'17, A ] ;
Inner Join
Load
MonthYear,
Count(If(NewFlag=1,NewFlag)) as NewCount
Resident Temp Group By MonthYear;
Note: In real data you might have to consider the load order also.
Hi,
Following code will work:
T:
LOAD * where not Exists (Product);
LOAD * Inline
[
MonthYear,Product
Aug17,A
Aug17,B
Aug17,C
Sep17,A
Sep17,D
];
NoConcatenate
T2:
LOAD MonthYear,count(Product) as Product
Resident T
Group by MonthYear;
DROP Table T;
if you just add new row to the inline load Dec17, B, the script will not work any more
hi Chhavi,
do you always need to compare only current and previous month?
it doesn't matter how many years of data you have, the result always should be one row - current Month and count. Is it correct?
or you want to compare month in each row with prev. month?
MonthYear,Product
Aug17,A
Aug17,B
Aug17,C
Sep17,A
Sep17,D
Dec17,B
the result should be
Aug17, 3
Sep17, 1
Dec17, 2
Yes Because B is there in Month of Aug17
do you always need to compare only current and previous month? NO. I need to compare all the months with their previous
Aug17,A
Aug17,B
Aug17,C
Sep17,A
Sep17,D
Oct17,A
Oct17,D
Nov17,A
Nov17,D
Nov17,E
Dev17,F
Dec17,G
TableResult:
Aug 3
Sep 1
Oct 0
Nov 1
Dec 2
Hi,
For this logic use below code:
T:
LOAD *
where not Exists (productflag);
LOAD *,if(RecNo()=1,MonthYear&Product,if(MonthYear = Peek(MonthYear),MonthYear&Product,peek(MonthYear)&Product)) as productflag,RecNo() Inline
[
MonthYear,Product
Aug17,A
Aug17,B
Aug17,C
Sep17,A
Sep17,D
Dec17,B
];
NoConcatenate
T2:
LOAD MonthYear as M,count(Product) as P
Resident T
Group by MonthYear;
If that is the case then my first code will work for the same.
If your case is resolved then close this thread and mark the correct answer correct.
really?
Did you get the result that was provided?
or just a little but modify the data
Aug17,A
Aug17,B
Aug17,C
Sep17,A
Sep17,D
Oct17,A
Oct17,D
Nov17,A
Nov17,F
Nov17,E
Dec17,F
Dec17,G
TableResult:
Aug 3
Sep 1
Oct 0
Nov 2
Dec 1
here is a code
T:
LOAD *
Inline
[
MonthYear,Product
Aug17,A
Aug17,B
Aug17,C
Sep17,A
Sep17,D
Oct17,A
Oct17,D
Nov17,A
Nov17,F
Nov17,E
Dec17,F
Dec17,G
];
T2:
NoConcatenate
load MonthYear,
len(PurgeChar(List,Previous(List))) as Count;
load MonthYear,Concat(Product) as List
Resident T
Group by MonthYear;
drop Table T;
just one note: you MonthYear should be sorted correctly