topic create a variable in QlikView

create a variable

drohm002 — Wed, 25 Nov 2020 16:16:04 GMT

Hi, please see my table below. I want to create a variable under these conditions: H_DAYS must = 1, M_DAYS must be between 1 and 90. And if these parameters are true I want the variable to return 1, or count only 1 row, whichever is easier. So ID 3256 meets all the conditions, but I don't want a count of 3, I want a count of 1, or a variable that returns the number 1. Please help!

ID	D_DATE	DI_DATE	H_DAYS	M_DAYS
3256	7/2/2018	6/29/2018	1	4
3256	7/2/2018	6/29/2018	1	4
3256	7/2/2018	6/29/2018	1	4
7789	6/29/2018	3/15/2018	1	106
7789	6/29/2018	3/15/2018	1	106
7789	6/29/2018	3/15/2018	1	106

Re: create a variable

datagrrl — Mon, 13 Aug 2018 13:56:54 GMT

Maybe I am thinking too much, but do you want to count 1 is the ID ever has records matching that criteria?

ID	D_DATE	DI_DATE	H_DAYS	M_DAYS
3256	7/2/2018	6/29/2018	1	4
3256	7/2/2018	6/29/2018	1	91
3256	7/2/2018	6/29/2018	1	4

Like would this situation be 0 because it has a record that doesn't meet the criteria, or would it still be one? Or does this not happen?

Re: create a variable

marcus_sommer — Mon, 13 Aug 2018 14:23:03 GMT

Maybe: count({< H_DAYS = {1}, M_DAYS = {">=1<=90"}>} distinct ID)

- Marcus

Re: create a variable

drohm002 — Mon, 13 Aug 2018 16:22:50 GMT

this actually does happen, I just encountered it! I would like to select the MIN for M_DAYS for each specific ID to base the formula on. would you be able to help?

Re: create a variable

drohm002 — Mon, 13 Aug 2018 16:27:24 GMT

hi marcus, what if M_DAYS has different values for the same ID, how can I write the formula so that it is basing the calculation off of the minimum value for that specific ID?

Re: create a variable

marcus_sommer — Tue, 14 Aug 2018 06:36:33 GMT

I think it's depending on your datamodel but you could try:

count({< H_DAYS = {1}, M_DAYS = {"=min(M_DAYS)>=1 and min(M_DAYS)<=90"}>} distinct ID)

An alternatively might be to use a flag within the script maybe with something like:

t: load ID, min(M_DAYS) as X from Source where H_DAYS = 1 group by ID;

M: mapping load ID, 1 from resident t where X >=1 and X <=90;

T: load *, applymap('M', ID, 0) as Flag from Source;

- Marcus