Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | |
---|---|---|---|
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 | |
---|---|---|---|
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 | |
---|---|---|---|
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
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?
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
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;