Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
chhavi376
Creator II
Creator II

New products not present in previous month

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




1 Solution

Accepted Solutions
tresesco
MVP
MVP

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;

Capture.JPG

Note: In real data you might have to consider the load order also.

View solution in original post

19 Replies
sushil353
Master II
Master II

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;

t_chetirbok
Creator III
Creator III

if you just add new row to the inline load Dec17, B, the script will not work any more

t_chetirbok
Creator III
Creator III

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

sushil353
Master II
Master II

Yes Because B is there in Month of Aug17

chhavi376
Creator II
Creator II
Author

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

sushil353
Master II
Master II

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;

sushil353
Master II
Master II

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.

t_chetirbok
Creator III
Creator III

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

t_chetirbok
Creator III
Creator III

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