topic Re: Update, Append, Purge, Composite key in QlikView

Update, Append, Purge, Composite key

userid128223 — Thu, 13 Feb 2014 20:27:40 GMT

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

Re: Update, Append, Purge, Composite key

Nicole-Smith — Thu, 13 Feb 2014 20:46:15 GMT

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.

Re: Update, Append, Purge, Composite key

userid128223 — Thu, 13 Feb 2014 20:55:14 GMT

Thanks Nicole for quick reply.

2 follow-up questions.

Does the uniq key in this whole scenerio matters, i dont see any thing to compute uniq key or use it in code.
Will this always fetch the new updated records even if they exist in Source.qvd.
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;

Re: Update, Append, Purge, Composite key

Nicole-Smith — Thu, 13 Feb 2014 21:06:05 GMT

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.

Re: Update, Append, Purge, Composite key

userid128223 — Thu, 13 Feb 2014 21:14:15 GMT

What does Group by EmpID, CreateDate do?

Re: Update, Append, Purge, Composite key

userid128223 — Thu, 13 Feb 2014 22:05:54 GMT

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;

Re: Update, Append, Purge, Composite key

Nicole-Smith — Fri, 14 Feb 2014 13:31:31 GMT

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.

Re: Update, Append, Purge, Composite key

userid128223 — Fri, 14 Feb 2014 14:30:27 GMT

Thanks Nicole.

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

Re: Update, Append, Purge, Composite key

Nicole-Smith — Fri, 14 Feb 2014 14:35:58 GMT

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.

Re: Update, Append, Purge, Composite key

userid128223 — Fri, 14 Feb 2014 14:46:32 GMT

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

Re: Update, Append, Purge, Composite key

Nicole-Smith — Fri, 14 Feb 2014 14:50:05 GMT

If you're new to SQL, start by going through this tutorial (http://www.w3schools.com/sql/default.asp?PHPSESSID=300ae3404d5fa2612f238abeebb8869c). It's pretty simple and doesn't dive in very deep, but it should give you a good start.

If any of the answers above are correct or helpful, please mark them so that others can find solutions to their problems too

Re: Update, Append, Purge, Composite key

userid128223 — Fri, 14 Feb 2014 14:54:38 GMT

I did thanks, your solutions are simple easy to understand and to the point.

Thanks.

Re: Update, Append, Purge, Composite key

userid128223 — Fri, 14 Feb 2014 14:56:19 GMT

So if you did not have update date and only wanted to update records based on uniq key then you will use

where not exist (key)

however because we had update date in the mix this time, you had to do inner join and group by clause.

Re: Update, Append, Purge, Composite key

Nicole-Smith — Fri, 14 Feb 2014 14:59:42 GMT

We could have gone the exists/not exists route for this example as well, but it works better using the inner join because it will take the newest data whether it's in the Daily or Source file. (It's also less code to do it with the inner join.)

Re: Update, Append, Purge, Composite key

userid128223 — Fri, 14 Feb 2014 15:08:51 GMT

out of curiosity i tried this. I get all the records. why please explain

INNER JOIN (FINAL)

LOAD EmpID, EmpName, Amount, TerminalNo, CreateDate, max(UpdateDate) as UpdateDate RESIDENT FINAL

GROUP BY EmpID, EmpName, Amount, TerminalNo, CreateDate;

Re: Update, Append, Purge, Composite key

Nicole-Smith — Fri, 14 Feb 2014 15:10:55 GMT

You shouldn't be including Amount and TerminalNo, they make every row unique. You only want the unique rows for EmpID, EmpName, CreateDate, and max(UpdateDate).

Re: Update, Append, Purge, Composite key

userid128223 — Fri, 14 Feb 2014 15:16:50 GMT

make sense amazing.

Re: Update, Append, Purge, Composite key

userid128223 — Fri, 14 Feb 2014 15:18:33 GMT

dont give me solution but just give me theory if you were to approach this using where not exist. how would you solve it.

In these kind of situation is there a rule of thumb, do you have theory on that. if its this then do that kind of scenario.

Re: Update, Append, Purge, Composite key

Nicole-Smith — Fri, 14 Feb 2014 18:28:07 GMT

Load all from Source adding a key for unique lines (EmpID and CreateDate). Then load all from Daily where same key does not exist in Source. Then you still need the inner join to get latest update date.

Re: Update, Append, Purge, Composite key

Nicole-Smith — Fri, 14 Feb 2014 18:29:45 GMT

Looking at your data, it seems that everything in Source is also in Daily, so why not just load Daily in for the newest UpdateDate? (Maybe your example isn't like your actual data, but if it is, it seems like the Source part isn't even needed since all data would exist in Daily anyway.)