Loop to load a file every monday

[mikegauntlett]

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. 

[Nuckpio]

Hi Mike !

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

Regards.

[mikegauntlett]

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. 🙂

[mikegauntlett]

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');