2 Replies Latest reply: Oct 5, 2012 5:19 AM by bhancerli RSS

    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

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

          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

               

          ;