topic Re: Help needed for better solution in QlikView

Help needed for better solution

arasantorule — Tue, 31 Oct 2017 07:07:46 GMT

Dear Friends,

We have a requirement, where we need to achieve the snapshot load every month. (Load the snapshot of the entire data by end of every month).

Example :

Feb 2017:

User From To Department

A 20/02/2016 12/12/9999 Finance

B 18/03/2016 12/12/9999 Accounts

March 2017:

User From To Department

A 20/02/2016 12/12/9999 Finance

B 18/03/2016 12/12/9999 Accounts

C 12/10/2017 12/12/9999 HR

Apr 2017:

User From To Department

A 20/02/2017 12/12/9999 Accounts

B 18/03/2016 12/12/9999 Accounts

C 12/10/2017 12/12/9999 HR

Expected Output during April 2017 data reload:

User From To Department Asof

A 20/02/2016 19/02/2017 Finance Feb2017

A 20/02/2017 12/12/9999 Accounts Apr2017

B 18/03/2016 12/12/9999 Accounts Feb2017

A 20/02/2017 12/12/9999 Accounts Apr2017

B 18/03/2016 12/12/9999 Accounts Mar2017

C 12/10/2017 12/12/9999 HR Mar2017

A 20/02/2017 12/12/9999 Accounts Apr2017

B 18/03/2016 12/12/9999 Accounts Apr2017

C 12/10/2017 12/12/9999 HR Apr2017

Scenario : In the above example, User A's record is updated in April 2017. However, it needs to be corrected from Feb 2017 during the April reload.

Challenge: We do not have a last modified time-stamp in our data.

could you please help with better solutions on this? Currently I am comparing each rows of previous month with latest month to achieve the result.

Thanks

Re: Help needed for better solution

sunny_talwar — Tue, 31 Oct 2017 11:48:01 GMT

Not sure I understand the reason for changing this from Feb? Also, how did you get to 19/02/2017 and not another date? Is this date specific to user?

Re: Help needed for better solution

arasantorule — Wed, 01 Nov 2017 01:42:21 GMT

Hi Sunny,

The record for Staff 'A' should have been updated on 20th Feb 2017 to reflect the department movement from Finance to Accounts.

However, it is a human error that, they missed to update on Feb and updated only on April.

Since we need to show the correct numbers from February, we require to change the department changes from February for the staff A.

Also the earlier record for Staff A on Feb is updated to end on "19/Feb/207". Because from 20/Feb/2017 the Staff has moved to Accounts Department.

Thanks a lot.

Re: Help needed for better solution

sunny_talwar — Wed, 01 Nov 2017 08:48:44 GMT

But just by looking at this data how do I know that A changed in Feb? I mean what if had B changed to Finance in Apr 2017 file? Would have have said that he made the change in Feb also? I am just not sure that how do we decide when they made the switch and how is the date decided?

Re: Help needed for better solution

sunny_talwar — Wed, 01 Nov 2017 08:49:36 GMT

Oh, I see it now.... the date on the Apr file say 20/02/2017... I was missing the year in that file... this makes complete sense now.... will work on it and get back to you

Re: Help needed for better solution

arasantorule — Mon, 29 Jan 2018 02:35:15 GMT

Thanks Sunny..