Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Thanks. There are other text feilds that i need to bring in. How can i do that?
like
Title,
Owner,
Changed Date
etc