Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
hobanwashburne
Creator
Creator

Incremental Load, Date Format, SQL Select

My overall goal is to read in the max date value from a previously created QVD then use that date in a SQL statement to pull in any data created after that date.

So far I am using the following script to get the Max Date:

temp:
LOAD ShipDate
FROM

(qvd);

MaxDate:
LOAD max(ShipDate) as MaxDate;

load FieldValue('ShipDate', IterNo()) as ShipDate
AutoGenerate(1)
While not IsNull(FieldValue('ShipDate', IterNo()));

Drop Table temp;

This return a table named MaxDate containing one field named MaxDate having the value 41670 (which translates to 1/31/2014)

The problem is that the database that I am pulling the new information from uses the date format YYYYMMDD. So somehow I need to translate 41670 into 20140131 and write it into a variable so that I can use it in the following:

//Load existing QVD

US:
LOAD SalesOrder,
     SalesOrderKey,
     ReqShipDate,
     ShipDate
FROM

(qvd);

//Add new Data
      Concatenate LOAD
      VLSLSO & '-' & VLSLSL as SalesOrder,
      VLCOMP & '-' & VLSLSO & '-' & VLSLSL as SalesOrderKey,
      Date#(VLRDAT,'YYYYMMDD') as ReqShipDate,
      Date#(VLSDAT, 'YYYYMMDD') as ShipDate;
SQL SELECT *
FROM networkpath.TableName

where VLSDAT > MaxDate;

1 Solution

Accepted Solutions
Nicole-Smith

temp:
LOAD ShipDate
FROM

(qvd);

MaxDate:
LOAD max(ShipDate) as MaxDate;

load FieldValue('ShipDate', IterNo()) as ShipDate
AutoGenerate(1)
While not IsNull(FieldValue('ShipDate', IterNo()));

Drop Table temp;

LET vMaxDate = date(PEEK('MaxDate', 0, 'MaxDate'), 'YYYYMMDD');

DROP TABLE MaxDate;

//Load existing QVD

US:
LOAD SalesOrder,
     SalesOrderKey,
     ReqShipDate,
     ShipDate
FROM

(qvd);

//Add new Data
      Concatenate LOAD
      VLSLSO & '-' & VLSLSL as SalesOrder,
      VLCOMP & '-' & VLSLSO & '-' & VLSLSL as SalesOrderKey,
      Date#(VLRDAT,'YYYYMMDD') as ReqShipDate,
      Date#(VLSDAT, 'YYYYMMDD') as ShipDate;
SQL SELECT *
FROM networkpath.TableName

where VLSDAT > $(vMaxDate);

View solution in original post

2 Replies
Nicole-Smith

temp:
LOAD ShipDate
FROM

(qvd);

MaxDate:
LOAD max(ShipDate) as MaxDate;

load FieldValue('ShipDate', IterNo()) as ShipDate
AutoGenerate(1)
While not IsNull(FieldValue('ShipDate', IterNo()));

Drop Table temp;

LET vMaxDate = date(PEEK('MaxDate', 0, 'MaxDate'), 'YYYYMMDD');

DROP TABLE MaxDate;

//Load existing QVD

US:
LOAD SalesOrder,
     SalesOrderKey,
     ReqShipDate,
     ShipDate
FROM

(qvd);

//Add new Data
      Concatenate LOAD
      VLSLSO & '-' & VLSLSL as SalesOrder,
      VLCOMP & '-' & VLSLSO & '-' & VLSLSL as SalesOrderKey,
      Date#(VLRDAT,'YYYYMMDD') as ReqShipDate,
      Date#(VLSDAT, 'YYYYMMDD') as ShipDate;
SQL SELECT *
FROM networkpath.TableName

where VLSDAT > $(vMaxDate);

View solution in original post

linusblomberg
Creator II
Creator II

I had this same problem once. I solved it by converting the format of my max date variable in the Select statement.

Where FULL_DATE >= to_date('$(LastReloadDate)','MM/DD/YYYY');