Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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];
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
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;
hi antonio ,
nov should have only bcd . as we need to compare every other month's name with first month(sep)
regards
You wrote :
count of distinct names for nov which were present in oct.
Regards,
Antonio
my bad, it should be count of distinct names for nov which were present in oct and also in sep
Thanks and Regards