Update existing QVD

[Kumar6872]

Hello All,

I need some help in this.

We received a file daily with the following data

*Day 1 File

Id           Status      File_Change_Date

ABC        Initial       Jan-22-2018

* I do the following while i load my QVD with the additional columns.

Id           Status      S_Initial_Date      S_Work_In_Progress_Date      S_Completion_Date

ABC        Initial       Jan-22-2018

*Day 2 File

Id             Status                              File_Change_Date

ABC        Work In Progress             Feb-2-2018

* I do the following while i load my QVD with the additional columns.

Id           Status                      S_Initial_Date      S_Work_In_Progress_Date      S_Completion_Date

ABC        Work In Progress                                            Feb-2-2018

*Day 3 File

Id             Status                              File_Change_Date

ABC        Complete                        March-15-2018

* I do the following while i load my QVD with the additional columns.

Id           Status                      S_Initial_Date      S_Work_In_Progress_Date      S_Completion_Date

ABC        Complete                                                                                                                March-15-2018

Now my QVD looks like this

Id           Status                          S_Initial_Date       S_Work_In_Progress_Date      S_Completion_Date

ABC        Initial                            Jan-22-2018

ABC        Work In Progress                                                  Feb-2-2018

ABC        Complete                                                                                                                March-15-2018

Question: Is there any way where i can update the qvd and looks something like this?

(Basically i want to update the status changed date to the respective coloumn when the status is changed)

Id           Status                          S_Initial_Date       S_Work_In_Progress_Date      S_Completion_Date

ABC        Complete                        Jan-22-2018                       Feb-2-2018                             March-15-2018

[ignacio_pena]

Hello,

One idea would be to load the qvd that you currently have in 3 parts (one per day). Then you would have to join with LEFT JOIN. Finally you should export the new table in a new QVD.

I think that for the following loads, you should be able to see if you can load the QVD from days 2 and 3 with LEFT JOIN, instead of concatenating them.

I leave an example of the script.

///DAY 1
DEF:
LOAD Id,
Status,
S_Initial_Date
FROM
YOUR_ACTUAL_FILE.qvd
(qvd) WHERE NOT ISNULL(S_Initial_Date);

//DAY 2
LEFT JOIN(DEF)
LOAD Id,
S_Work_In_Progress_Date
FROM
YOUR_ACTUAL_FILE.qvd
(qvd) WHERE NOT ISNULL(S_Work_In_Progress_Date);

///DAY 3
LEFT JOIN(DEF)
LOAD Id,
S_Completion_Date
FROM
YOUR_ACTUAL_FILE.qvd
(qvd) WHERE NOT ISNULL(S_Completion_Date);

NoConcatenate
DEF_OK:
LOAD
Id,
IF(NOT ISNULL(S_Completion_Date), 'Complete', IF(NOT ISNULL(S_Work_In_Progress_Date), 'Work In Progress', 'Initial')) AS Status,
S_Initial_Date,
S_Work_In_Progress_Date,
S_Completion_Date
FROM DEF;

DROP TABLE DEF;

store * from DEF_OK into FINAL.qvd

a greeting