Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am new to the Qlikview development world and have a basic question:
I would like to create a new field (a new column in a table) from existing fields. I've read in the data from ODBC and I don't have any write permission nor do I want to write to that database.
I would lıke to make the changes in what Qlikview calls a resindent table. I tried using the LET expression but no new field was created.
Here is the code:
stok:
SQL SELECT a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant,
sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,
sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt
WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt
ELSE 0*a.sh_qty_mt END)as standartStok
FROM DBA.stkhar AS a
INNER JOIN DBA.mam AS b
ON a.sh_ma_kod = b.ma_kod
WHERE a.sh_ma_kod ='ME1005-01' AND a.sh_sf_no1 = 2012/* AND a.sh_ds_kod = '122'*/ AND a.sh_sf_ar_no = 20
GROUP BY a.sh_ma_kod, a.sh_ds_kod, a.sh_vry
;
Load *
Resident stok;
let kMetrajStok = $(brutstok)-$(standartstock)
Thanks for your help.
Berti
stok:
Load
*,
brutStok-standartStok as NEWFIELDNAME;
SQL SELECT a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant,
sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,
sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt
WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt
ELSE 0*a.sh_qty_mt END)as standartStok
FROM DBA.stkhar AS a
INNER JOIN DBA.mam AS b
ON a.sh_ma_kod = b.ma_kod
WHERE a.sh_ma_kod ='ME1005-01' AND a.sh_sf_no1 = 2012/* AND a.sh_ds_kod = '122'*/ AND a.sh_sf_ar_no = 20
GROUP BY a.sh_ma_kod, a.sh_ds_kod, a.sh_vry
;
stok:
Load
*,
brutStok-standartStok as NEWFIELDNAME;
SQL SELECT a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant,
sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,
sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt
WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt
ELSE 0*a.sh_qty_mt END)as standartStok
FROM DBA.stkhar AS a
INNER JOIN DBA.mam AS b
ON a.sh_ma_kod = b.ma_kod
WHERE a.sh_ma_kod ='ME1005-01' AND a.sh_sf_no1 = 2012/* AND a.sh_ds_kod = '122'*/ AND a.sh_sf_ar_no = 20
GROUP BY a.sh_ma_kod, a.sh_ds_kod, a.sh_vry
;
Thanks Johannes didn't realize it was that simple.