Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
So below I have an inline table and a concatenated load.
let vLogStart = now();
WORK_ORDER_FILTER:
LOAD * Inline [Work_Order_Status
Closed
Completed
Integrated
Not Completed
On Hold
On Hold - Customer Driven
On Hold - Elekta Driven
Open
PM Merged
Processing
Submitted
Superceded
Waiting for Parts
];
CALL TimeLogger('WO Filter');
let vLogStart = now();
//-----------------------------------------------------------------------------------------
WO_1Temp:
LOAD
Work_Order_Owner_Id_Key,
Work_Order_Name,
Work_Order_Created_By_Id_Key,
Work_Order_Status,
Work_Order_Type,
Work_Order_Description,
Work_Order_SFP_Id_Key AS OF_Id_Key_PRO,
Work_Order_Work_Description,
Work_Order_Start_Date as Work_Order_Start_Date1,
Work_Order_Account_Id_Key
//Work_Order_Installation_Date as Work_Order_Installation_Date1
FROM [lib://CLM Snapshots (Weekly)/REP_Work_Order_2019-01-07.qvd] //---Change Date to next available REP_Work_Order_
(qvd)Where Exists(Work_Order_Status);
WO:
LOAD *,
'2019-01-07' as Snapshot_Date,//---Change Date to match that of snapshot
DATE(Work_Order_Start_Date1,'dd-MMM-yyyy') as Work_Order_Start_Date
//DATE(Work_Order_Installation_Date1,'dd-MMM-yyyy') as Work_Order_Installation_Date
Resident WO_1Temp;
DROP TABLEs WO_1Temp;
DROP FIELDS Work_Order_Start_Date1;//Work_Order_Installation_Date1,Work_Order_Start_Date1;
//-----------------------------------------------------------------------------------------
WO_2Temp:
LOAD
Work_Order_Owner_Id_Key,
Work_Order_Name,
Work_Order_Created_By_Id_Key,
Work_Order_Status,
Work_Order_Type,
Work_Order_Description,
Work_Order_SFP_Id_Key AS OF_Id_Key_PRO,
Work_Order_Work_Description,
Work_Order_Start_Date as Work_Order_Start_Date1,
Work_Order_Account_Id_Key
//Work_Order_Installation_Date as Work_Order_Installation_Date1
FROM [lib://CLM Snapshots (Weekly)/REP_Work_Order_2019-01-14.qvd]
(qvd)Where Exists(Work_Order_Status);
CONCATENATE (WO)
LOAD *,
'2019-01-14' as Snapshot_Date,
DATE(Work_Order_Start_Date1,'dd-MMM-yyyy') as Work_Order_Start_Date
//DATE(Work_Order_Installation_Date1,'dd-MMM-yyyy') as Work_Order_Installation_Date
Resident WO_2Temp;
DROP TABLEs WO_2Temp;
DROP FIELDS Work_Order_Start_Date1;//Work_Order_Installation_Date1
//-----------------------------------------------------------------------------------------
What I ideally want to achieve is to replace the date with a variable.
FROM [lib://CLM Snapshots (Weekly)/REP_Work_Order_2019-01-07.qvd]
Instead of having to duplicate script on a continuous basis.
A new weekly snapshot is created every Monday, so the next one will be,
FROM [lib://CLM Snapshots (Weekly)/REP_Work_Order_2019-05-20.qvd].
I have to be able to compare data from this week to last week etc.
I have no clue how to achieve this and would appreciate any help.
Many Thanks
Hi Mike,
This will do the same and concatenate all to one table.
let vLogStart = now();
WORK_ORDER_FILTER:
LOAD * Inline [Work_Order_Status
Closed
Completed
Integrated
Not Completed
On Hold
On Hold - Customer Driven
On Hold - Elekta Driven
Open
PM Merged
Processing
Submitted
Superceded
Waiting for Parts
];
CALL TimeLogger('WO Filter');
let vLogStart = now();
//-------------------------------------------------------------------------
let vWeekStart = weekstart(date(today(),'YYYY-MM-DD'));
//-------------------------------------------------------------------------
WO:
Load * Inline [
Snapshot_Date
]
;
Concatenate(WO)
LOAD
$(vWeekStart) as Snapshot_Date,
Work_Order_Owner_Id_Key,
Work_Order_Name,
Work_Order_Created_By_Id_Key,
Work_Order_Status,
Work_Order_Type,
Work_Order_Description,
Work_Order_SFP_Id_Key AS OF_Id_Key_PRO,
Work_Order_Work_Description,
DATE(Work_Order_Start_Date1,'dd-MMM-yyyy') as Work_Order_Start_Date
Work_Order_Account_Id_Key
FROM [lib://CLM Snapshots (Weekly)/REP_Work_Order_$(vWeekStart).qvd] (qvd) // With the variable is will change automatically in your current week to the monday (week start)
Where Exists(Work_Order_Status);
Jordy
Climber
Thanks Jordy,
Is there a way to format the date for vWeekStart as it appears to be loading as 13/05/2019 instead of 2019-05-13?
Hi Mike,
Yes sure to the following:
let vWeekStart = weekstart(date(today(),'DD/MM/YYYY'));
Jordy
Climber
Hi Jordy sorry
The format should be yyyy-MM-dd,
As in the first response however when it loads it states
"FROM [lib://CLM Snapshots (Weekly)/REP_Work_Order_13/05/2019.qvd] (qvd)"