Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
iamscribe
Contributor III
Contributor III

Compare daily datasets and include records where any column changed

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:

NameEEIDJobEmail
Jeff Jones111PAtest@test.com
Sam Taylor112PRtest1@test.com
Danielle Perkins113PRtest2@test.com

Today:

NameEEIDJobEmail
Jeff Jones111PAtest@test.com
Sam Taylor112PEtest1@test.com
Danielle Rogers113PRtest4@test.com
Kyle Thomas114PTtest3@test.com

Exported:

NameEEIDJobEmail
Sam Taylor112PEtest1@test.com
Danielle Rogers113PRtest4@test.com
Kyle Thomas114PTtest3@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

3 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
iamscribe
Contributor III
Contributor III
Author

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
Partner - Champion III
Partner - Champion III

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein