Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 tables A and B.
Table A has "Request Qty" field
Table B has 2 Date fields "ETA" and "Delivery Date".
There are some common fields between tables A and B.
I am loading tables A and B separately in my load script and letting Qlik do the join based on the common fields.
The following Set analysis expression does not filter at all.
Sum({<[ETA]={">$([Delivery Date])"}>} [Request Qty])
Similar expression using IF statement works fine.
SUM(If([ETA]>[Delivery Date], [Request Qty], 0))
What am I doing wrong?
I also tried the following variant:
Sum({<[ETA]={">$(=Date([Delivery Date]))"}>} [Request Qty])
Using this expression yields empty results.
I am using QS Desktop tool (3.1 SR1)
"=...." is an expression search
if the Key is associated to one ETA and one Delivery date
every Kye value that meets te condition (ETA > delivery) will be returned
if you a have a key field in Table 2 (if not you can create the key in the script, with autonumber)
Sum({<Key = {"=(ETA > [Delivery Date])" } >} [Request Qty])
How does this work? ETA > Delivery Date will return -1 or 0 or 1 right?
How will it match an autonumber?
May be create a flag in the script:
Table2:
LOAD ETA,
[Delivery Date],
If(ETA > [Delivery Date], 1, 0) as Flag
FROM ....;
and then this use this expression
Sum({<Flag = {1}>} [Request Qty])
OR
You can avoid Set analysis by using like this :
Sum(Flag * [Request Qty])
Thanks
Jayant
"=...." is an expression search
if the Key is associated to one ETA and one Delivery date
every Kye value that meets te condition (ETA > delivery) will be returned