Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
aheavy95
Creator
Creator

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

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

 

 

Labels (4)
3 Replies
Jebrezov
Contributor III
Contributor III

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
Creator III
Creator III

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.