Replace one record in the data with another

[SilviyaK]

Hi all,

I have a logic built in Qlik and I gather registrant's details on a daily basis and I store them in daily QVDs. The registrant information contains a lot of details about the registration, including the response. The response can be 'accepted' or 'declined'. I gather the details about the registrants in daily QVDs based on the registration date. For example the QVD for 13th of March will contain all people who registered on 13th of March and their responses. 

I noticed an issue with this though. I can have a person who registered on 13th of March and responded 'Accepted', but on 16th of March changed their response to 'Declined'. In my QVD the response will remain 'Accepted', which is incorrect. I am now planning on how to gather the people who edited their responses, but I am not sure how to make Qlik understand that I want to replace the previous entry. 

For example if a person registered on 13th of March and responded 'Accepted' but later on appears in the data with another responde 'declined', I want Qlik to replace the accepted with declined, so that this person doesn't appear in my 'accepted' list. 

Any ideas how to do that? 

[Or · Accepted solution]

This seems like it should be a case of Not Exists

TempTable:

Load All QVDs;

FinalTable:

NoConcatenate Load * Resident TempTable

Where Not Exists(PersonID)

Order by Date desc;

Drop Table TempTable;

You can skip the resident if you can load the QVDs newest-to-oldest.

You could also achieve the same thing by using e.g.

Noconcatenate Load PersonID, Date(Max(Date)) as Date, FirstSortedValue(Response,-Date) as Response

resident TempTable

Group By PersonID;

[SilviyaK]

Hi! 

My first thought was this too, but as some people register for different activities, there might be multiple lines with the same PersonID related to different activities and I will need to have them all. I only need to make sure that if there is a line that is exactly the same as another one (same person, same activity, same amount paid) but one is with 'accepted' status and another is with 'declined' that the 'declined' status will prevail. 

Any other suggestions? 🙂 

[Or]

Same thing but with a concatenation / autonumber of whatever the key is. You could also just group by all the fields except Status and then get the LastSortedValue of Status.

[SilviyaK]

Now I understood what you meant! Thanks a lot, you saved me a lot of struggling! 🙂