topic Need a column value based on other columns in QlikView

Need a column value based on other columns

qlikuser2023 — Fri, 07 Apr 2023 03:03:18 GMT

I've the below table

TYPE	START_DATE	END_DATE	DE_ID	DE_LEN
IN	3/1/2023	3/1/2023	1	10
IN	3/2/2023	3/29/2023	2	23
OUT	3/4/2023	3/20/2023	3	15
OUT	3/4/2023	3/21/2023	3	20
IN	3/2/2023	4/1/2023	4	20
OUT	1/2/2023	4/1/2023	3	21

I need to get the total del_len for de_id for type = 'out' and de_id is between 3/1/2023 and 3/30/2023

I tried this but not getting incorrect data:

SUM( distinct if(MasterDate >= DATE(START_DATE,'MM/DD/YYYY') and MasterDate <= DATE(END_DATE,'MM/DD/YYYY') and TYPE = 'OUT', IF(DE_ID, DE_LEN)) )

MasterDate is from the date picker

Re: Need a column value based on other columns

BrunPierre — Fri, 07 Apr 2023 07:35:15 GMT

Like this, perhaps.

SUM( distinct Aggr( if(MasterDate >= DATE(START_DATE,'MM/DD/YYYY') and MasterDate <= DATE(END_DATE,'MM/DD/YYYY') and TYPE = 'OUT', DE_ID), DE_LEN) )

Re: Need a column value based on other columns

qlikuser2023 — Fri, 07 Apr 2023 11:55:55 GMT

Nope, not working. It's returning 0.

I'm able to get a count of DE_IDs using this:

COUNT(DISTINCT {<TYPE={'OUT'}>} if(MasterDate >= DATE(START_DATE,'MM/DD/YYYY') and MasterDate <= DATE(END_DATE,'MM/DD/YYYY'), DE_ID))

so I need the sum(DE_LEN) for all those DE_IDs

Re: Need a column value based on other columns

BrunPierre — Fri, 07 Apr 2023 18:31:25 GMT

Does this not work with the same parameters?

~~COUNT~~ Sum(~~DISTINCT~~ {<TYPE={'OUT'}>} if(MasterDate >= DATE(START_DATE,'MM/DD/YYYY') and MasterDate <= DATE(END_DATE,'MM/DD/YYYY'), ~~DE_ID~~ DE_LEN))

Re: Need a column value based on other columns

qlikuser2023 — Fri, 07 Apr 2023 18:52:10 GMT

No, since I cannot do a distinct DE_LEN (this will cause some values to drop off). The values could be like this

DE_ID	DE_LEN
12	10
10	10
11	20
13	11

I need all the DE_LEN for all DE_ID

Re: Need a column value based on other columns

BrunPierre — Fri, 07 Apr 2023 19:06:36 GMT

Create a variable for these DE_IDs
LET vDE_IDList = 'Concat(DISTINCT {<TYPE={'OUT'}>} if(MasterDate>= DATE(START_DATE,'MM/DD/YYYY') and MasterDate <= DATE(END_DATE,'MM/DD/YYYY'), DE_ID,', '))' ;

And use it within the expression
=SUM({<DE_ID={$(vDE_IDList)}>} DE_LEN)

Re: Need a column value based on other columns

qlikuser2023 — Mon, 10 Apr 2023 15:41:05 GMT

Thank you. This worked.