topic Re: Loop to load a file every monday in App Development

Loop to load a file every monday

mikegauntlett — Wed, 22 May 2019 09:10:53 GMT

Hi Guys,

So i have file that is made available to me every monday. From the 2019-01-07

I have set up variables to get the week start and the file path.

But now I need to load and concatenate the data.

So week one I have one weeks of data with the date, week two two weeks of data and two dates.

here is the script so far

let vLogStart = now(); WORK_ORDER_FILTER: LOAD * Inline [Work_Order_Status Closed Completed 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 = date(weekstart(today()), 'YYYY-MM-dd'); let vFilePath = '[lib://CLM Snapshots (Weekly)/REP_Work_Order_' & date(weekstart(today()), 'YYYY-MM-dd') & '.qvd]'; //------------------------------------------------------------------------- WO: Load * Inline [ Snapshot_Date, ] ; Concatenate(WO) LOAD peek($(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_Date,'dd-MMM-yyyy') as Work_Order_Start_Date, Work_Order_Account_Id_Key FROM $(vFilePath)(qvd) Where Exists(Work_Order_Status); CALL TimeLogger('WO');

Any Help is greatly appreciated.

Re: Loop to load a file every monday

Nuckpio — Wed, 22 May 2019 09:56:47 GMT

Hi Mike !

Why don't you use the Qlik Management Console (QMC)? you can add tasks to reload your app every [x] minutes.

Regards.

Re: Loop to load a file every monday

mikegauntlett — Thu, 23 May 2019 09:45:31 GMT

Within the App I need to be able to compare data from one week to other week via the snapshot date.

Also I have no access to the QMC for our company. 🙂

Re: Loop to load a file every monday

mikegauntlett — Thu, 23 May 2019 12:32:56 GMT

Current Script

let vLogStart = now();

WORK_ORDER_FILTER:
LOAD * Inline [Work_Order_Status
Closed
Completed
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();

//-------------------------------------------------------------------------

Set vWeekStart = date(weekstart(today()), 'YYYY-MM-dd');
let vFilePath = '[lib://CLM Snapshots (Weekly)/REP_Work_Order_' & date(weekstart(today()), 'YYYY-MM-dd') & '.qvd]';

//-------------------------------------------------------------------------

Set a = date(weekstart(today()), 'YYYY-MM-dd');

Do while a < today();

WO_Temp:
LOAD
//peek($(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,
Work_Order_Start_Date as Work_Order_Start_Date0,
Work_Order_Account_Id_Key
FROM $(vFilePath)(qvd)
Where Exists(Work_Order_Status);

WO:
Load
*,
$(vWeekStart) as Snapshot_Date,
Date(Work_Order_Start_Date0, 'dd-MMM-YYYY') as Work_Order_Start_Date
Resident WO_Temp;

Drop Table WO_Temp;

Loop

CALL TimeLogger('WO');