topic Re: How to write LOAD in IF in Edit Script in QlikView? in QlikView

How to write LOAD in IF in Edit Script in QlikView?

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

How to write LOAD in IF in Edit Script in QlikView?

for example I use this query in SQL:

SELECT 
(CASE WHEN price != 0 THEN price
ELSE (SELECT Tbl.AVG_ FROM (SELECT AVG(price] AS int) AS AVG_, Year_, Month_
FROM myTable
GROUP BY Year_, Month_
) AS Tbl
WHERE Tbl.Year_ = Year_
) 
END)
,Product

FROM myTable

but I don't know how to use in qlikview?

Re: How to write LOAD in IF in Edit Script in QlikView?

sergio0592 — Sat, 02 Mar 2019 13:45:27 GMT

Hi,

Why you don't let your SQL query in your load statement with :

load *;
SQL
SELECT 
(CASE WHEN price != 0 THEN price
ELSE (SELECT Tbl.AVG_ FROM (SELECT AVG(price] AS int) AS AVG_, Year_, Month_
FROM myTable
GROUP BY Year_, Month_
) AS Tbl
WHERE Tbl.Year_ = Year_
) 
END)
,Product

FROM myTable;

Re: How to write LOAD in IF in Edit Script in QlikView?

hamy_IT_Dev — Sat, 02 Mar 2019 14:43:28 GMT

Hi Sergio0592

Thanks for reply

but I need a script like this:

Load 
(if price <> 0 , price,(load Tbl.AVG_ 
			         FROM (load AVG(price) AS AVG_, Year_, Month_
					      FROM myTable
					      GROUP BY  Year_, Month_
					      ) AS Tbl
				  WHERE Tbl.Year_ = Year_
				)	
END)
,Product

FROM myTable

Re: How to write LOAD in IF in Edit Script in QlikView?

marcus_sommer — Mon, 04 Mar 2019 14:31:53 GMT

A sub-query within a load isn't possible. You need to add those data per join/mapping to your origin load and perform there the matching, maybe with something like this:

m: mapping load Year & '|' & Month & '|' & Product as Lookup, avg(price) as Return
from Table1 group by Year & '|' & Month & '|' & Product;

load if(price <> 0, price, applymap('m', Year & '|' & Month & '|' & Product, '#NV')) as price, Product from Table2;

- Marcus