Update, Append, Purge, Composite key

[userid128223]

I have following data that has source QVD and daily qvd. Source needs to get modified on daily basis via updates from daily.qvd.

• Uniq key is composed of:  EmpID+EmpName+CreateDate
• Daily file will have duplicate data where only updateDate will indicate latest recods which needs to be kept.
• Data that is similar in daily.qvd needs to be purged out based on latest Updatdate.

Daily.qvd

EmpID	EmpName	Amount	TerminalNo	CreateDate	UpdateDate
123	Joe	$200	20	1/6/2014	1/6/2014
123	Joe	$100,000	20	1/6/2014	1/7/2014
123	Joe	$500,000	20	1/6/2014	1/9/2014
123	Joe	$400	40	1/8/2014	1/8/2014
235	Doe	$55	20	1/8/2014	1/22/2014
235	Doe	$200,000	20	1/8/2014	2/1/2014
235	Doe	$500	50	1/15/2014	1/15/2014
345	Kim	$456	56	1/9/2014	1/9/2014
724	Mele	$25	20	1/18/2014	1/18/2014
887	Duwane	$15	30	1/22/2014	1/22/2014

Source.QVD

EmpID	EmpName	Amount	TerminalNo	CreateDate	UpdateDate
123	Joe	$200	20	1/6/2014	1/6/2014
123	Joe	$400	40	1/8/2014	1/8/2014
235	Doe	$55	20	1/8/2014	1/22/2014
235	Doe	$200,000	20	1/8/2014	2/1/2014
345	Kim	$456	56	1/9/2014	1/9/2014

FINAL RESULTS:

EmpID	EmpName	Amount	TerminalNo	CreateDate	UpdateDate
123	Joe	$500,000	20	1/6/2014	1/9/2014
123	Joe	$400	40	1/8/2014	1/8/2014
235	Doe	$200,000	20	1/8/2014	2/1/2014
345	Kim	$456	56	1/9/2014	1/9/2014
724	Mele	$25	20	1/18/2014	1/18/2014
887	Duwane	$15	30	1/22/2014	1/22/2014

[Nicole-Smith · Accepted solution]

This should do the trick:

Final:

LOAD * FROM Daily.qvd (qvd);

CONCATENATE (Final) LOAD * FROM Source.qvd (qvd);

INNER JOIN (Final) LOAD EmpID, CreateDate, max(UpdateDate) as UpdateDate RESIDENT Final GROUP BY EmpID, CreateDate;

Example files also attached.

[userid128223]

Thanks Nicole for quick reply.

2 follow-up questions.

1. Does the uniq key in this whole scenerio matters, i dont see any thing to compute uniq key or use it in code.
2. Will this always fetch the new updated records even if they exist in Source.qvd.
3. Can you please explain how your code works. I was following you up until Concatenate. Please explain below.
   1. INNER JOIN (Final) LOAD EmpID, CreateDate, max(UpdateDate) as UpdateDate RESIDENT Final GROUP BY EmpID, CreateDate;

[Nicole-Smith]

1.  I don't need to create a key because we're not linking the tables.  We're joining and concatenating on fields that exist in both files and that are named the same, so there is no need to concatenate the three fields together.

2.  It should.

3.  We start by just concatenating all data together (as you said you understand).  Then the inner join is used to keep everything that doesn't have the max(UpdateDate) for each Employee/CreateDate combination.

[userid128223]

What does Group by EmpID, CreateDate do?

[userid128223]

Nicole I tried your solution and it works great  However I still have hard time understanding it. I will try below and you correct me or fill in the gaps.

Final:

LOAD * FROM Daily.qvd (qvd);    // Load all the data from Daily.qvd

CONCATENATE (Final) LOAD * FROM Source.qvd (qvd);   // Load all the data to above tables and append any new found records.

At this point we have this table.

EmpID	EmpName	Amount	TerminalNo	CreateDate	UpdateDate
123	Joe	$200.00	20	1/6/2014	1/6/2014
123	Joe	$400.00	40	1/8/2014	1/8/2014
123	Joe	$100,000.00	20	1/6/2014	1/7/2014
123	Joe	$500,000.00	20	1/6/2014	1/9/2014
235	Doe	$55.00	20	1/8/2014	1/22/2014
235	Doe	$500.00	50	1/15/2014	1/15/2014
235	Doe	$200,000.00	20	1/8/2014	2/1/2014
345	Kim	$456.00	56	1/9/2014	1/9/2014
724	Mele	$25.00	20	1/18/2014	1/18/2014
887	Duwane	$15.00	30	1/22/2014	1/22/2014

I am lost after this......

After this INNER JOIN (FINAL)   // how does it look here at this point.

LOAD EmpID, CreateDate, max(UpdateDate) as UpdateDate RESIDENT Final GROUP BY EmpID, CreateDate;

[Nicole-Smith]

The Group By clause is needed because max() is an aggregation function.

We are loading EmpID, CreateDate, and max(UpdateDate) grouped by EmpID and CreateDate since you want the newest UpdateDate for each EmpId and CreateDate.  By inner joining only the newest UpdateDate, EmpId, and CreateDate to the old table with all the data, it gets rid of the ones that don't have the newest UpdateDate.

[userid128223]

Thanks Nicole.

I need training in basics i guess. where do you go to learn these stuff.

[Nicole-Smith]

For me, it's been a combination of learning at work when I hit a problem as well as just reading posts on the community.

[userid128223]

For me its more of sql i think then qlikview. i think SQL is 50% of qlikview.