QVD Load

[Kumar6872]

Hello Community members,

I have the following requirement where i want to calculate the days it took  in different status.

I am receiving the following file 

ID             Status             CreateDate             WorkInProgressDate              CompletedDate
ABC           Initial             Jan 1 2019                      -                                                             -
ABC           WIP                       -                                 Jan 20 2019                                         -
ABC          Complete           -                                          -                                             Jan 31 2019

Is there anyway where i can load the QVD and have the data in the following manner.. that way i can easily calculate the duration between the status.

ID             Status             CreateDate             WorkInProgressDate              CompletedDate
ABC          Complete        Jan 1 2019              Jan 20 2019                                 Jan 31 2019 

[Alicja]

Hi,

You can try aggregating your dates by ID and calculating the status field, something along these lines:

LOAD
ID,
if(isnull(max(WorkInProgressDate)), 'Initial', if(isnull(max(CompletedDate)), 'WIP', 'Completed')) as Status,
max(CreateDate) as CreateDate,
max(WorkInProgressDate) as WorkInProgressDate,
max(CompletedDate) as CompletedDate
FROM (...)
group by ID;

[Kumar6872]

Thanks. There are other text feilds that i need to bring in. How can i do that?

like

Title,

Owner,

Changed Date

etc