topic Re: Update new data in QlikView

Update new data

madhubabum — Mon, 30 Nov 2015 06:23:05 GMT

Hi Experts

I am having the following data set

OLD_DataSet:

Wo_Pk Hours Status

100 15 Complete

101 20 Complete

105 30 Pending

107 40 Complete

108 30 Pending

NEW_DataSet:

Wo_Pk Hours Status

100 15 Complete

101 20 Complete

105 30 Complete

107 40 Pending

108 30 Complete

110 10 Complete

In the above highlighted records are modified ,

Here i want to update the records based upon the "Wo_Pk".(Based upon the requirement 'No need to create Common Key')

i) update newly added records

ii) Add newly added records from NEWDataSet

Please help me, to write the Script

Thanks

Madhu

Re: Update new data

Anonymous — Mon, 30 Nov 2015 06:37:41 GMT

Hi Madhu,

Any date field is there to track the changes in your source?

If so, you can use incremental load to achieve the above logic.

Regards,

Leni Balakrishnan

Re: Update new data

madhubabum — Mon, 30 Nov 2015 06:41:34 GMT

thanks for reply

I don't have date field, I have only 'Wo_Pk' as primary key

Re: Update new data

Kushal_Chawda — Mon, 30 Nov 2015 06:44:04 GMT

There should be Modified to pull updated data so that you can do the incremental load

See the below link

http://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Re: Update new data

Kushal_Chawda — Mon, 30 Nov 2015 06:45:49 GMT

When record is updated some field values gets modified, but in your case not even status is modified then how can you say it is modified?

Re: Update new data

jonathandienst — Mon, 30 Nov 2015 06:51:31 GMT

Your question is confusing.

>>In the above highlighted records are modified ,

No they are not -- There is no difference between old and new for the hightlighted lines, so I have no idea what you are trying to achieve.

>>i) update newly added records

>>ii) Add newly added records from NEWDataSet

What is the difference between these two? You cannot update them if they have not been added, and why do you need to update them if you have just added them?

Re: Update new data

madhubabum — Mon, 30 Nov 2015 06:56:54 GMT

Sorry Jonathan Dienst

OLD_DataSet:

Wo_Pk Hours Status

100 15 Complete

101 20 Complete

105 30 Pending

107 40 Complete

108 30 Pending

NEW_DataSet:

Wo_Pk Hours Status

100 15 Complete

101 20 Complete

105 30 Complete

107 40 Pending

108 30 Complete

110 10 Complete

this is my data set

Re: Update new data

jonathandienst — Mon, 30 Nov 2015 07:11:15 GMT

How can you tell, without reading the entire data set, what has changed?

Are the Wo_PK value continually increasing (so that new Wo_Pk values will be greater than older ones)? And is there some status which will indicate that no more updates are coming through (eg Complete)?

If both of these conditions are satisfied, then you can do an update style incremental load by doing the following:

Determine the lowest possible Wo_Pk value in thw QVD that is not complete.
Load from your database everything with a W0_Pk greater than or equal to this number.
Load from the QVD everything with a W0_Pk less than this number.
Save the result in the QVD.

It is not as efficient as having a last updated date, as you will often be reading unchanged statuses from the database, but it may still be worthwhile because you do not need to load completed Wo_Pk values.

Re: Update new data

madhubabum — Mon, 30 Nov 2015 07:45:16 GMT

Thanks for reply ,

In my data set values are changing like 110,115,90,67,...150,....

How we can update and insert records based on "Wo_Pk"

Re: Update new data

jonathandienst — Mon, 30 Nov 2015 07:50:58 GMT

Then an update type incremental load is not possible as you have to load the entire dataset anyway to determine what has changed.

Re: Update new data

jyothish8807 — Mon, 30 Nov 2015 08:30:54 GMT

Hi Madhu,

try like this:

OLD_DataSet:

LOAD *,1 as index INLINE [

Wo_Pk, Hours, Status

100, 15, Complete

101, 20, Complete

105, 30, Pending

107, 40, Complete

108, 30, Pending

];

LOAD *,2 as index INLINE [

Wo_Pk, Hours, Status

100, 15, Complete

101, 20, Complete

105, 30, Complete

107, 40, Complete

108, 30, Complete

];

inner join (OLD_DataSet)

load

Wo_Pk,

max(index) as index

resident OLD_DataSet group by Wo_Pk;

Regards