topic Synthetic Key in Incremental Load in QlikView

Synthetic Key in Incremental Load

gauravs275 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am trying to perform an incremental load by using a wildcard character to load all the files in a folder. I am using a composite key to act as a primary key. On reloading, I am getting a synthetic key issue. There is a document attached that can give you an idea about what I have done.

Re: Synthetic Key in Incremental Load

Vegar — Mon, 15 Jul 2019 06:58:17 GMT

There is not document attached. Could you please re-attach it?

Re: Synthetic Key in Incremental Load

Brett_Bleess — Wed, 07 Aug 2019 18:07:39 GMT

Gaurav,

Sorry I am not much direct help here, but I am going to drop a few links that might possibly help and hopefully Vegar may be able to circle back as well. Unfortunately, the platform does not make it very easy for us to be sure we do not miss any follow-up posts, something we are trying to work on.

Design Blog:

Synthetic keys:
https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634

Incremental Load:
https://community.qlik.com/t5/Qlik-Design-Blog/Overview-of-Qlik-Incremental-Loading/ba-p/1466780

Help:

Qualify:
https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptRegularStatements/Qualify.htm

Unqualify:
https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptRegularStatements/Unqualify.htm

The last two above will be a way to try to be sure that synthetic key does not create...

Regards,
Brett

Re: Synthetic Key in Incremental Load

Vegar — Wed, 07 Aug 2019 19:40:46 GMT

@gauravs275 It can be tricky with preceeding loads in combination with LOAD ... FROM *, you will often end up with multiple "identical" tables.

I would suggest you try to change your FROM wildchar* script into a loop that reads all files one at the time.

for each vFile in FileList('C:\Users\Administrator\Desktop\TRN_BI_KPI_CP2022_PastData\TRN_BI_KPI_CP2022_*.tsv') [Transaction]: LOAD ... ; LOAD ... ; LOAD ... ; LOAD [Year Id]&'-'&[Reporting Month Id]&'-'&[Block Id]&'-'&[Pillar Id]&'-'&[KPI Id] as %Key, [Year Id], Date(Date#([Reporting Month Id],'M'),'MMM') AS [Reporting Month], [Block Id], [Pillar Id], [KPI Id], [Country Code] AS Market, [Latest Flag], Sum([Target Amount]) AS [Target Amount], Sum([Actual Amount]) AS [Actual Amount], RowNo() as RowId FROM [$(vFile)] (txt, unicode, embedded labels, delimiter is '\t', msq) where match([Latest Flag],1) Group By [Year Id], [Reporting Month Id], [Block Id], [Pillar Id], [KPI Id], [Country Code], [Latest Flag]; Next vFile

You will probably get trouble with the LEFT JOIN FROM Wildchar* as well so it is better to first create and fill the table you want to use for the join before executing the JOIN command.

/*Creating the table you want to use for join*/ TMP: LOAD [Year Id]&'-'&[Reporting Month Id]&'-'&[Block Id]&'-'&[Pillar Id]&'-'&[KPI Id] as %Key, [Root Cause], Countermeasure, [KPI Result Forecast], RowNo() as RowId2 //You will probably not want to join on ths field name FROM [C:\Users\Administrator\Desktop\TRN_BI_KPI_CP2022_PastData\TRN_BI_KPI_CP2022_*.tsv] /*When all data is collected then do the join*/ LEFT Join (Transactions) LOAD * Resident TMP; /*Drop the TMP as you don't need it in your data model*/ Drop Table TMP;

Good luck with solving you problem.

If these suggestions nor @Brett_Bleess links doesn't help you, then you will probably need to be more specific and share more details in order to get the community to help you.