Suggest an Idea

[simonaubert]

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

Person	Manager	Sales	ChangeDate
Maxime	Thierry	110	01/01/2021
Arnaud	Myriam	120	02/01/2021
Thierry	Chef Suprême	240	03/01/2021
Myriam	Chef Suprême	260	04/01/2021
Simon	Myriam	110	05/01/2021
Fabrice	Thierry	130	06/01/2021
Benoît	Myriam	130	07/01/2021
Adrien	Myriam	100	01/01/2020
Chef Suprême	666	02/01/2020
Muriel	Myriam	123	01/01/2021
Muriel	Myriam	124	01/02/2021

Données pour Merge

Operation	Person	Manager	Sales	ChangeDate
Update	Maxime	Thierry	160	01/02/2021
Update	Arnaud	Myriam	180	02/01/202
Delete	Adrien	Myriam	100	01/01/2020
Update	Maxime	Thierry	170	01/03/2021
Update	Coralie	Myriam	172	01/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 :

ChangeDate	Person	Sales	Manager
02/01/2021	Arnaud	120	Myriam
07/01/2021	Benoît	130	Myriam
02/01/2020	Chef Suprême	666	-
06/01/2021	Fabrice	130	Thierry
01/03/2021	Maxime	170	Thierry
01/01/2021	Muriel	123	Myriam
01/02/2021	Muriel	124	Myriam
04/01/2021	Myriam	260	Chef Suprême
05/01/2021	Simon	110	Myriam
03/01/2021	Thierry	240	Chef 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.

Best regards,

Simon

[AlexOmetis]

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]

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

[Patric_Nordstrom]

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

[Meghann_MacDonald]

From now on, please track this idea from the Ideation portal. 

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.