qlik

[Anonymous]

Hi All,

i have data below data and i want to calculate count of distinct individual for sep , then count of distinct name for oct which were present in sep. similarly  count of distinct names for nov which were present in oct.

input 

year	month	name
2011	sep	abc
2011	sep	bcd
2011	sep	cde
2011	oct	abc
2011	oct	bcd
2011	oct	efg
2011	nov	bcd
2011	nov	efg
2011	nov	fgh

output

2011	sep	3
2011	oct	2
2011	nov	1

thanks and regards

[petter · Accepted solution]

In your load script you can calculate the counts like this:

[DATA]:
LOAD 
* 
INLINE 
[year,month,name
2011,sep,abc
2011,sep,bcd
2011,sep,cde
2011,oct,abc
2011,oct,bcd
2011,oct,efg
2011,nov,bcd
2011,nov,efg
2011,nov,fgh
] 
WHERE
Exists(name) OR (year = 2011 AND month='sep')
;

[DATA SUMMARY]:
LOAD
year as y, month as m, Count(DISTINCT name) AS count
RESIDENT
[DATA]
GROUP BY
year, month;

DROP TABLE [DATA];

[MarcoWedel]

Hi,

maybe one solution might be:

table1:
LOAD *,
    MonthName(MakeDate(year,month)) as YearMonth;
LOAD year,
    Month(Date#(month,'MMM')) as month,
    name
FROM [https://community.qlik.com/thread/277984] (html, codepage is 1252, embedded labels, table is @1);

table2:
LOAD *,
    AutoNumberHash128(YearMonth,name) as YearMonthNameID,
    -(Exists(YearMonthNameID,AutoNumberHash128(AddMonths(YearMonth,-1),name)) or AutoNumber(YearMonth)=1) as InPreviousMonth
Resident table1
Order By YearMonth;

DROP Table table1;

hope this helps

regards

Marco

[antoniotiman]

Another way

Temp:
LOAD year,Month(Date#(month,'MMM')) as month,name
FROM
https://community.qlik.com/message/1361852
(html, codepage is 1252, embedded labels, table is @1);
LOAD *,If(year=Peek(year) and name=Peek(name) or AutoNumber(year&month)=1,1) as Flag
Resident Temp
Order By name,year,month;
Drop Table Temp;

[Anonymous]

hi antonio ,

nov should have only bcd . as we need to  compare every other month's name with first month(sep)

regards

[antoniotiman]

You wrote :

count of distinct names for nov which were present in oct.

Regards,

Antonio

[Anonymous]

my bad, it should be count of distinct names for nov which were present in oct and also in sep

Thanks and Regards