Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to create a new field in a table from existing fields.

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

1 Solution

Accepted Solutions
jsn
Honored Contributor

Re: How to create a new field in a table from existing fields.

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

     

;

2 Replies
jsn
Honored Contributor

Re: How to create a new field in a table from existing fields.

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

     

;

Not applicable

Re: How to create a new field in a table from existing fields.

Thanks Johannes didn't realize it was that simple.

Community Browser