Concatenate to table only rows that does not exist based on key column

[aheavy95]

Hi all 
In a script for Inventory calculation ,

I wish to add rows from a table only in cases when they do not exist in the first table based on an id.

Here is the script:

Inventory_tbl_temp:

    load 

        SURI_WRIN,

        StoreID,

        addmonths(Sale_Year_Month,1) as Sale_Year_Month,

        StockAct_Cls as StockAct_Opn,

        CostStockAct_Cls as CostStockAct_Opn,

        AutoNumberHash256(SURI_WRIN,StoreID,Sale_Year_Month) as Item_store_ym_key

        ,Num(StoreID,'000')&'-'&SURI_WRIN&'-'&addmonths(Sale_Year_Month,1) as Fact_Summ_Key

        ,Num(StoreID,'000')&'-'&SURI_WRIN&'-'&addmonths(Sale_Year_Month,1) as Fact_Summ_Key_comp

from  [lib://$(ERD_Inventory_Temp)/Rolling/Inventory_tbl_YM.QVD](qvd);

   ;

 A:   

//add new items

Concatenate(Inventory_tbl_temp)

load 

StoreID,

    "SURI_WRIN",

    StockAct_Opn,

    Sale_Year_Month,

    CostStockAct_Opn,

    Fact_Summ_Key,

    1 as flagIsNewKey

from  [lib://$(DB)/InvKeysAll_OpenStock.QVD](qvd)

where Sale_Year_Month='$(YM)' and not exists(Fact_Summ_Key);

This doesn't solve my issue since I still get 2 rows of the same key at the end.
I saw an old post - https://community.qlik.com/t5/QlikView-App-Dev/Concatenate-Load-Where-New-ID-Not-Exists/td-p/785812

in which it was recommended to add another key field (I added as "Fact_Summ_Key_comp")

and add it to the not exists in the where clasue like this : not exists(Fact_Summ_Key, Fact_Summ_Key_comp);

But the column is not declared in table A so I don't get how this can be implemented.

Anyways- how can I achieve this?

Thanks

[Jebrezov]

I was actually just looking at this yesterday. I don’t really get it myself yet, but below is an article I found. It seems like you need to rename the original key column to a temporary name and use that as your first iD in the where not exist clause.

https://community.qlik.com/t5/Official-Support-Articles/WHERE-NOT-EXISTS-clause-returns-only-one-row... 

[Ahidhar]

try something like this , create a composite key and apply distinct to remove duplicates

tab:
load ID,Value;
load * inline
[ID,Value
1,10
1,12
2,13
2,10
3,11
3,13
3,15
];
concatenate
load ID,Value;
load * Inline
[
ID,Value
2,13
2,10
2,13
2,10
4,10
3,14
];

NoConcatenate
tab1:
load Distinct ID,Value,key;
load ID,Value,
Autonumber(ID&Value) as key
resident tab;
drop table tab;

[marcus_sommer]

I think your approach should be working without doubling the field or renaming them or using exists() with a second parameter. If not it hints more for issues with the data-quality respectively the data are different to your expectation.

To detect it you could add an extra SOURCE field to each load with values like 'Inv' and 'Open' and also a rowno() as RowNo. Then use a table-box within the UI with these fields and your key to see how many identically key-values are there and from which source they are coming.