Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jenmclean
Not applicable

Load last 90 days of data into QVD

I want to only load the last 90 days of data into a QVD. The following is my script. How would I accomplish this without specifiying a date?

LOAD DISTINCT
 
VendorTransID as VND|TransID,
    
VendorTransType as VND|TransType,
    
Applymap('TransTypeMap',VendorTransType, Null()) as VND|TransTypeDesc,
   
Date(DayStart(VendorInvDate), 'M/DD/YYYY') as VND|InvDate, //Entered Date

     VendorPONumber as VND|PONumber,

    
VendorPaidFlag as VND|PaidFlag,

    
VendorCheckNumber as VND|CheckNum,

    
Applymap('UserIDMap',VendorEnteredBy, Null()) as VND|UserName,

    
VendorInvNumber as VND|InvNumber,

    
VendorLocation as KEY|StoreNumber,

    
Date(DayStart(VendorEnteredDate), 'M/DD/YYYY') as VND|InvEntDate

FROM



(
qvd)

WHERE(VendorInvDate >= '4/1/2014');



LEFT JOIN (VendorInvoices)

LOAD DISTINCT

 
VendorTransID as VND|TransID,

    
VendorID as VND|VendorID,

    
VendorInvAmount as VND|InvAmount

FROM



(
qvd);



LEFT JOIN (VendorInvoices)

LOAD DISTINCT

 
VendorID as VND|VendorID,

    
VendorAcctNo as VND|VendorAcctNo,

    
VendorName as VND|VendorName

FROM



(
qvd);

3 Replies
Gysbert_Wassenaar
Not applicable

Re: Load last 90 days of data into QVD

Something like this:

LET vDate = num(today())-90;

MyTable:

Load ... from xxxx.qvd (qvd)

where VendorInvDate > $(vDate);


talk is cheap, supply exceeds demand
Not applicable

Re: Load last 90 days of data into QVD

Hi,

Try this:

LET vMinDate = '4/1/2014';  // or LET vMinDate = Date((Today()-90),'MM/DD/YYYY');

// Take care with Date format from your Fact Table, if VendorInvDate has values like '04/01/2014' you have to use same format or VendorInvDate has values like 'apr/01/2014' you have to specify this format...

FactTable:

Load

          Field1,

          Field2,

          FieldN

from myFacttable.qvd

WHERE VendorInvDate >= '$(vMinDate)';  // Is very important  ' '  to specify a date value

Left Join(FactTable)

Rest of script....

Best Regards.

jenmclean
Not applicable

Re: Load last 90 days of data into QVD

Thanks, I'll ty that