Qlik Community

Suggest an Idea

Vote for your favorite Qlik product ideas and add your own suggestions.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING

Merge : upsert (insert if new otherwise update..) as new type of operation

simonaubert
Partner
Partner

Merge : upsert (insert if new otherwise update..) as new type of operation

Hello all,

I'm in the process of reworking our entire "qlik etl/preparation" to use the new function Merge
For reference, here the help :
https://help.qlik.com/fr-FR/sense/May2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...

However, a deception occured when I tested it.


here is my data:

Données init

PersonManagerSalesChangeDate
MaximeThierry11001/01/2021
ArnaudMyriam12002/01/2021
ThierryChef Suprême24003/01/2021
MyriamChef Suprême26004/01/2021
SimonMyriam11005/01/2021
FabriceThierry13006/01/2021
BenoîtMyriam13007/01/2021
AdrienMyriam10001/01/2020
Chef Suprême66602/01/2020
MurielMyriam12301/01/2021
MurielMyriam12401/02/2021

 

Données pour Merge

OperationPersonManagerSalesChangeDate
UpdateMaximeThierry16001/02/2021
UpdateArnaudMyriam18002/01/202
DeleteAdrienMyriam10001/01/2020
UpdateMaximeThierry17001/03/2021
UpdateCoralieMyriam17201/03/2021

 

My code

 

 

NoConcatenate
LOAD_Données_Init:
LOAD
    Person,
    Manager,
    Sales,
    ChangeDate
FROM [lib://REPO_10_STATIC_CONTENT/Content/SCF_TEST_MAY2021/ClasseurMergeetOrgchart.xlsx]
(ooxml, embedded labels, table is [Données init]);

NoConcatenate
LOAD_Données_Merge:
LOAD
    Operation as operation_toto,
    Person,
    Manager,
    Sales,
    ChangeDate
FROM [lib://REPO_10_STATIC_CONTENT/Content/SCF_TEST_MAY2021/ClasseurMergeetOrgchart.xlsx]
(ooxml, embedded labels, table is [Données pour Merge]);

Merge(ChangeDate)
On Person concatenate(LOAD_Données_Init)
LOAD * RESIDENT LOAD_Données_Merge;

Drop table LOAD_Données_Merge;

 

 



And the result :

ChangeDatePersonSalesManager
02/01/2021Arnaud120Myriam
07/01/2021Benoît130Myriam
02/01/2020Chef Suprême666-
06/01/2021Fabrice130Thierry
01/03/2021Maxime170Thierry
01/01/2021Muriel123Myriam
01/02/2021Muriel124Myriam
04/01/2021Myriam260Chef Suprême
05/01/2021Simon110Myriam
03/01/2021Thierry240Chef Suprême

 

Hey, wait a minute.... I just lost Coralie !!

Short explanation  : if you choose update as the operation type, it won't insert any new record.

What a frustration ! I don't want to find if it's a new record... I just want to automatically insert new record and update old ones if necessary.

that's what this idea is all about.

simonaubert_0-1632298348859.jpeg

 


Best regards,

Simon

Bi Consultant (Dataviz & Dataprep) @ Business & Decision
Tags (2)
3 Comments
AlexOmetis
Partner
Partner

What you describe sounds like upsert, which would be useful to have.

Of course it's fairly easy to work around by doing something like this using where exists, but agree an upsert function would be simpler:

NoConcatenate
LOAD_Données_Init:
LOAD
    Person,
    Manager,
    Sales,
    ChangeDate
FROM [lib://REPO_10_STATIC_CONTENT/Content/SCF_TEST_MAY2021/ClasseurMergeetOrgchart.xlsx]
(ooxml, embedded labels, table is [Données init]);

NoConcatenate
LOAD_Données_Merge:
LOAD
    Operation as operation_toto,
    Person,
    Manager,
    Sales,
    ChangeDate,
    Person as Person_incoming
FROM [lib://REPO_10_STATIC_CONTENT/Content/SCF_TEST_MAY2021/ClasseurMergeetOrgchart.xlsx]
(ooxml, embedded labels, table is [Données pour Merge]);

Merge(ChangeDate)
On Person concatenate(LOAD_Données_Init)
LOAD * RESIDENT LOAD_Données_Merge
Where exists(Person,Person_incoming);

Insert(ChangeDate)
On Person concatenate(LOAD_Données_Init)
LOAD * RESIDENT LOAD_Données_Merge
Where not exists(Person,Person_incoming);

Drop field Person_incoming;
Drop table LOAD_Données_Merge;

 

simonaubert
Partner
Partner

@AlexOmetis 
Thanks. I found on another turnaround with a Autogenerate(0) to initialize the table. Works fine too.

Patric_Nordstrom
Employee
Employee

Thank you all for your feedback on ways to improve our product. While this is something we understand would be useful, it's not on the short-term roadmap. Please continue to show your support for this idea by commenting and liking.

Thanks, 
Patric

Status changed to: Open - Collecting Feedback