Duplication of lines while updating field after a join..

[#1]

Hı all,

I am in desperate need of help with the code below. I am loading two tables from ODBC, then joining them and updating the value of a field based on certain conditions.

up until the sipstok: label I don't have any problems. But the last load duplicates certain rows. I tried using the noconcatenate expression but it didn't help.

Because of the duplication updateing the value of the standartstok field doesn't work properly. Any help is highly appreciated.

Thanks.

ODBC CONNECT32 TO RAPOR;

siparis:

SQL SELECT ol_or_no1, ol_or_no2, ol_sr, ol_ma_kod AS ma_kod, ol_qty, ol_kapa, ol_ds_kod AS desen, ol_vry AS varyant, ol_plan_onay, ol_wrk_basdt, ol_onaydt,

                               ol_or_anhno, ol_onay2, ol_onay2dt,or_pe_no, or_anhno, or_rr_no

          FROM DBA.ordln

          JOIN DBA.ord

          ON ordln.ol_or_anhno = ord.or_anhno

          WHERE ol_kapa = 'A' AND ol_or_no1 = 2012 AND ol_ma_kod = 'ME1005-01'  /*AND ol_onay2 <> '          */

          order by ol_or_no2, ma_kod, desen, varyant

;

stok:

Load

     *,

     brutStok-standartStok as kMetrajStok;

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

       LEFT 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

       order by ma_kod, desen, varyant

;

LEFT JOIN (siparis) LOAD * resident stok;

sipstok:

NoConcatenate

LOAD

                              ol_or_no2,ol_sr, ma_kod, desen, varyant, ol_qty,

                                        if(rowno()=1,

                                     standartStok,

                                             if(ma_kod = previous(ma_kod) AND desen = previous(desen) and varyant = previous(varyant) ,

                                          Previous(standartStok)- Previous(ol_qty), standartStok)

                                           ) as standartStok,

                                                  if(

                                                  if(ma_kod=previous(ma_kod) AND desen = previous(desen) and varyant = previous(varyant) ,

                                          Previous(standartStok)-Previous(ol_qty), standartStok)>=ol_qty, now(),

                                          ) AS planTarih

resident siparis;

[Miguel_Angel_Baeyens]

Hi,

The NOCONCATENATE prefix is used when you don't want to append current table rows to any previously loaded table, as long as both have the same number and name of fields, which it does not seem to be the case.

In the second place, use the LOAD statement on top of the SQL SELECT to control what you are actually putting into QlikView memory. Field names are case sensitive, so CustomerID is not the same as customerid. You likely need to control that as well.

Hope that helps.

Miguel

[#3]

actually it was the previous expression that caused the issue when replaced with peek() it seems to work fine. I will try the LOAD as well.

[#4]

Dear Use Distinct function and keep in min it will work.like that

Load Distinct Date as Date_Join

What ever the field is .

Hope answer the Question.

[#5 · Accepted solution]

The actual answer was the peek expression. I've tried the distinct but that didn't work. Apperantly qlikviews associative database, but previous gets the data directly from the input source. Doing that fixed the problem.

Thanks for the help