Create QVD with the oldest Record

Anonymous — Wed, 19 Apr 2017 16:16:01 GMT

Example data:

Amount	Transaction Date	CUSTOMER_ACCOUNT_ID
1478.74	11/30/16 6:00	1
456	11/30/16 8:00	1
154	12/1/16 0:00	2
1897.45	12/3/16 0:00	1
4538	12/3/16 0:00	3

I want the ONLY the older record from each ID saved in the QVD.

Initially I will have a QVD with all historic, then I want run this everyday and have it add only the customer ID's that dont exist.

PLEASE HELP.

LET vStartDate = MakeDate(2016,12,01);

LET vEndDate = MakeDate(2016,12,03);

FOR vDayNo = vStartDate to vEndDate;

LET vDate = Date(vDayNo, 'MM/DD/YYYY');

Data:

LOAD

Amount as AMOUNT,

"Transaction Date" as TRANSACTION_DATE,

CUSTOMER_ACCOUNT_ID

FROM [lib://AttachedFiles/Test.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE Date("Transaction Date") = Date('$(vDate)') ;

NoConcatenate

Temp1:

LOAD

TRANSACTION_DATE,

CUSTOMER_ACCOUNT_ID,

sum( AMOUNT) AS [AMOUNT]

RESIDENT Data

WHERE AMOUNT <> 0

GROUP BY

TRANSACTION_DATE,CUSTOMER_ACCOUNT_ID;

NoConcatenate

FirstLoadTemp:

LOAD

FirstSortedValue(AMOUNT, (TRANSACTION_DATE)) as Amount,

Date(FLOOR(TRANSACTION_DATE)) as [Transaction Date],

CUSTOMER_ACCOUNT_ID,

CUSTOMER_ACCOUNT_ID2

Resident Temp1

Group by Date(FLOOR(TRANSACTION_DATE)), CUSTOMER_ACCOUNT_ID;

CONCATENATE(FirstLoad)

LOAD *

FROM [lib://folder\Test.qvd](qvd)

WHERE NOT EXISTS (CUSTOMER_ACCOUNT_ID2, CUSTOMER_ACCOUNT_ID);

DROP FIELD CUSTOMER_ACCOUNT_ID2;

STORE FirstLoad INTO [lib://folder\Test.qvd];

DROP TABLE Data;

Drop TABLE FirstLoad;

DROP TABLE Temp1;

NEXT vDayNo

Re: Create QVD with the oldest Record

Anonymous — Wed, 19 Apr 2017 16:29:34 GMT

swuehl I have seen you reply to post similar to that. Can you please look into this.
THANK YOU!

Re: Create QVD with the oldest Record

Anonymous — Wed, 19 Apr 2017 18:30:18 GMT

@stalwar1 Can you please please please help me with this. I have seen you help people with all sorts of things.
please. thanks!

topic Re: Create QVD with the oldest Record in QlikView

Create QVD with the oldest Record

Re: Create QVD with the oldest Record

Re: Create QVD with the oldest Record