Automating a concatenated data load

[mikegauntlett]

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

[JordyWegman]

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

[mikegauntlett]

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?

[JordyWegman]

Hi Mike,

Yes sure to the following:

let vWeekStart = weekstart(date(today(),'DD/MM/YYYY'));

Jordy

Climber 

[mikegauntlett]

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)"