how to use variables to automate load process

[Anonymous]

Hi Experts!

I have file received every month contains all the procedures done in the hospital in the past 3 years. And every month I use this file to create another file which sums up the total number of patients and procedures  by hospital id and each month I have to manually add in the new month, and concatenate load the previous month. below is a part of the script. DOS stands for Date of Service.

Any suggestions how can I automate this process, so the script can add the new month every time running it?

Thanks!

Let x = Timestamp(Now(),'YYYYMMDD');

LOAD

   Hopstial_ID,   

     count(distinct Person_ID) as patients_count,

     count(PVL_VisitID) as procedure_count,

  '03/31/2017' as post_period

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq) where DOS>='4/1/2016' and DOS<='03/31/2017' group by Hopstial_ID;

Concatenate

LOAD

     Hopstial_ID,   

     count(distinct Person_ID) as patients_count,

     count(PVL_VisitID) as procedure_count,

  '02/28/2017' as post_period

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq) where DOS>='3/1/2016' and DOS<='02/28/2017' group by Hopstial_ID;

Concatenate

LOAD

   Hopstial_ID,   

     count(distinct Person_ID) as patients_count,

     count(PVL_VisitID) as procedure_count,

  '01/31/2017' as post_period

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq) where DOS>='2/1/2016' and DOS<='01/31/2017' group by Hopstial_ID;

[Oleg_Troyansky]

Hi Ze,

your logic is rather easy to implement. While I can't spend all the time to write the whole script for you, I can give you some pointers.

You need to implement a FOR loop, iterate the loop by a desired number of months and calculate your desired Start and End dates for the load statement. Something like this:

vLastMonthEnd = MonthStart(Today()) - 1;   // The date of the previous month end

FOR i = 1 to 12 // loading 12 snapshots, changes as needed

   let vEnd = Date(AddMonths(vLastMonthEnd, -i));   // Each step goes 1 month back

   let vStart = Date(AddMonths(vEnd, -12) + 1);               // Start Date =End Date - 12 months + 1 day.

   MyTable:

   LOAD

     Hopstial_ID,  

     count(distinct Person_ID) as patients_count,

     count(PVL_VisitID) as procedure_count,

     '$(vEnd)' as post_period

   FROM

   (txt, utf8, embedded labels, delimiter is '\t', msq)

   where DOS>='$(vStart)' and DOS<='$(vEnd)'

   group by Hopstial_ID;

NEXT

This is pretty close to what you need. You might just need to polish some rough edges.

We teach these advanced scripting techniques at the Masters Summit for Qlik. Please visit our site and determine if our agenda is a good fit for you.

Cheers,

Oleg Troyansky

Upgrade your Qlik Skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

[Anonymous]

This is perfect~

Thank you!