topic Populate record from excel source in QlikView

Populate record from excel source

Tue, 24 Aug 2010 09:32:29 GMT

Hi,
Hope you can help me, i need to know how I can populate records from excel, please see sample records below:

CUST NO | POST DATE | TARGET

100000 | 2010/05/01 | 50,000
200000 | 2010/01/01 | 10,000

i wanted to load the records above and populate it and store in a QVD with the following results:

100000 | 2010/05/01 | 50,000
100000 | 2010/06/01 | 50,000
100000 | 2010/07/01 | 50,000
100000 | 2010/08/01 | 50,000
200000 | 2010/01/01 | 10,000
200000 | 2010/02/01 | 10,000
200000 | 2010/03/01 | 10,000
200000 | 2010/04/01 | 10,000
200000 | 2010/05/01 | 10,000
200000 | 2010/06/01 | 10,000
200000 | 2010/07/01 | 10,000
200000 | 2010/08/01 | 10,000

Records above are populate base on the CURRENT DAY and the POSTDATE.

Please help.

Tnx

Populate record from excel source

Tue, 24 Aug 2010 22:58:47 GMT

Hi,m

try the code below. because i'musing a german QV i changed the "," of the Target to a "." 😉

The idea ist to use the iterno() function to multiply a single record into multiple records.

org:
LOAD * INLINE [
CUST_NO, POST_DATE, TARGET
100000, 2010/05/01, 50.000
200000, 2010/01/01, 10.000
];

LET vDateToday= NUM(MAKEDATE(2010,1, 8));

new:
NOCONCATENATE
LOAD
CUST_NO
, DATE(NUM(DATE#(POST_DATE,'YYYY/DD/MM'))+ iterno() - 1) AS POST_DATE
, TARGET
RESIDENT org
WHILE iterno() <= ($(vDateToday) - NUM(DATE#(POST_DATE,'YYYY/DD/MM'))+1);

DROP TABLE org;

Best regards

Michael

Populate record from excel source

Wed, 25 Aug 2010 07:48:28 GMT

Hi Michael,

The code works perfect, however my iteration is is base on months and not on days as defined in your code.

my date format is YYYY/MM/DD that might have confused you. Hope you can still help me on this. tnx