Monthly data comparison and finding newly added items

Wed, 18 Mar 2015 10:49:55 GMT

Hello!

I am currently working on one problem which seems to be complicated to me. I have monthly data which I need to compare. I always compare two neighbour month(9&10, 10&11,etc). Every month new items are added to the list of existing ones and I new to find and count them. My current table structure looks like this:

Table1. Total = 3 items

Item	Field1	Field2	MonthNo
A	1	1	10
B	2	1	10
C	2	3	10

Table2. Total = 6 items

Item	Field1	Field2	MonthNo
A	1	1	11
B	2	1	11
C	2	3	11
F	1	2	11
D	2	1	11
G	2	2	11

So the task is to find new items added in each month (always comparing latest and previous months) which meet the criteria: Field1=2 and count them. Answer in this case(months 11 and 10): Items count=2 (Items D and G meet the criteria).

I can get the monthly total number of distinct items and also number of items where Field1=2 but I need to compare the actual items codes in each month, find new ones(that didn’t exist in previous month) and then count them. Is there any solution for this problem?

Re: Monthly data comparison and finding newly added items

Gysbert_Wassenaar — Wed, 18 Mar 2015 12:47:06 GMT

Try searching for new+lost: http://community.qlik.com/search.jspa?q=new+lost

You should encounter for example these discussions:

http://community.qlik.com/message/678525#678525

http://community.qlik.com/message/262719#262719

That said you can easily identify new items in the load script if your source data is already ordered by month:

LOAD

Item,

Field1,

Field2,

MonthNo,

if(not exists Item, 1, 0) as IsNew,

if(Field1 = Field2) as IsEqualField1Field2

FROM ...source.

You can count the new items with count({<IsNew={1},IsEqualField1Field2={1}>}distinct Item)

topic Re: Monthly data comparison and finding newly added items in QlikView

Monthly data comparison and finding newly added items

Re: Monthly data comparison and finding newly added items