Compare daily datasets and include records where any column changed

[Anonymous]

We are trying to create a simple 'changes' file for one of our app projects.  The idea I had was to create a QVW load that has a variable for 'EvenDay' and 'OddDay', the variable is tied to an if statement to reload the same SQL script consisting of all of our employees included in the project.  This way if daynumber(today()) is odd it will store the results in the Odd QVD and visa versa for when day number is even.

Both datasets will have the exact same columns and will have an EEID key field.  I want to be able to compare the two datasets linking by the EEID to see if any data in any of the columns have changed between today and yesterday.  If something has changed (email, job, resigned, etc) then I want to keep only the records from today's dataset where the list of columns does not match yesterday.

Yesterday:

Name	EEID	Job	Email
Jeff Jones	111	PA	test@test.com
Sam Taylor	112	PR	test1@test.com
Danielle Perkins	113	PR	test2@test.com

Today:

Name	EEID	Job	Email
Jeff Jones	111	PA	test@test.com
Sam Taylor	112	PE	test1@test.com
Danielle Rogers	113	PR	test4@test.com
Kyle Thomas	114	PT	test3@test.com

Exported:

Name	EEID	Job	Email
Sam Taylor	112	PE	test1@test.com
Danielle Rogers	113	PR	test4@test.com
Kyle Thomas	114	PT	test3@test.com

I was thinking of just loading in both QVDs and concatenate them and say if yesterday.name <> today.name then today.name.  I just figure there is probably a function to use in Qlik to compare when you have a key field to rely on.

Thank you for the direction,

Phil

[jonathandienst]

For Sam Taylor you are exporting PR. Today's value if PE. Is that a typo?

Do you want to do this in the load or in the front end expressions? Do you want to keep the input tables or only the"exported" table?

[Anonymous]

Yes, sorry about that.  I am not trying to make anything in the front end, just a QVW to handle the comparison and I'm going to push the 'exported' set via REST.  I assumed I would do the comparison on load.  The input tables will not need to be kept, but I may have an appended QVD for anything in the 'exported' table so I can keep track of what has changed.

Fixed the typo.

Thank you

[jonathandienst]

Something like this:

Data:

LOAD EEID,

  Name as Name0,

  Job as Job0,

  Email as Email0,

  1 as Y_Flag

FROM .... yesterday .... (qvd);

Join(Data)

LOAD

  EEID,

  Name as Name1,

  Job as Job1,

  Email as Email1,

  1 as T_Flag

FROM .... today .... (qvd);

Compare:

LOAD

  EEID,

  If(Name0 <> Name1, Name1, Name0) as Name,

  If(Job0 <> Job1, Job1, Job0) as Job,

  If(Email0 <> Email1, Email1, Email0) as Job,

  'Changed'

Resident Data

Where YFlag = 1 And Tlag = 1

  And (Name0 <> Name1

  Or Job0 <> Job1

  Or Email0 <> Email1);

Concatenate(Compare)

LOAD

  EEID,

  Name1 as Name,

  Job1 as Job,

  Email1 as Email,

  'Added'

Resdident Data

Where TFlag = 1 And Len(YFlag) = 0;

Concatenate(Compare)

LOAD

  EEID,

  Name0 as Name,

  Job0 as Job,

  Email0 as Email,

  'Removed'

Resdident Data

Where YFlag = 1 And Len(TFlag) = 0;

DROP Table Data;