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

[hamy_IT_Dev]

Hi

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?

[sergio0592]

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;

[hamy_IT_Dev]

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

[marcus_sommer]

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