topic Re: Automating a concatenated data load in App Development

Automating a concatenated data load

mikegauntlett — Sat, 16 Nov 2024 05:48:17 GMT

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

Re: Automating a concatenated data load

JordyWegman — Fri, 17 May 2019 13:32:35 GMT

Hi Mike,

This will do the same and concatenate all to one table.

Jordy
Climber

Re: Automating a concatenated data load

mikegauntlett — Fri, 17 May 2019 13:52:00 GMT

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?

Re: Automating a concatenated data load

JordyWegman — Fri, 17 May 2019 13:55:25 GMT

Hi Mike,

Yes sure to the following:

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

Jordy

Climber

Re: Automating a concatenated data load

mikegauntlett — Fri, 17 May 2019 14:01:26 GMT

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