topic qlik in QlikView

qlik

Anonymous — Fri, 13 Oct 2017 14:12:51 GMT

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

Re: qlik

petter — Fri, 13 Oct 2017 15:46:43 GMT

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];

Re: qlik

MarcoWedel — Fri, 13 Oct 2017 22:07:47 GMT

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

Re: qlik

antoniotiman — Sat, 14 Oct 2017 07:27:59 GMT

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;

Re: qlik

Anonymous — Sat, 14 Oct 2017 09:11:59 GMT

hi antonio ,

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

regards

Re: qlik

antoniotiman — Sat, 14 Oct 2017 10:02:24 GMT

You wrote :

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

Regards,

Antonio

Re: qlik

Anonymous — Sun, 15 Oct 2017 07:18:17 GMT

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

Thanks and Regards