Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Synthetic Key in Incremental Load

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.

 

syn.png

3 Replies
Highlighted
Partner
Partner

Re: Synthetic Key in Incremental Load

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Digital Support
Digital Support

Re: Synthetic Key in Incremental Load

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/Scrip...

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

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

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Partner
Partner

Re: Synthetic Key in Incremental Load

@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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes