Delta Load issue: Generated QVDs of Field Values from Table

[Anonymous]

Hi evryone,

i want to use  Delta Load to generate the QVDs of each year from Table. e.g:

Datum	Feld1	Feld2	Feld3
01.01.2014	a	b	c
20.12.2015	b	d	e
04.11.2013	c	f	g
15.03.2012	ooo	fff	ieee
12.03.2012	d	h	i
15.08.2016	e	j	o

That is my code, but i got only the values from  2012.

I want to have  4 QVDs; KG2012.qvd, KG2013.qvd, KG2014.qvd, KG2015.qvd, KG2016.qvd

Delta:

LOAD Datum,

Year(Datum) as Jahr,

     Feld1,

     Feld2,

     Feld3

FROM

Delaload.xlsx

(ooxml, embedded labels, table is Tabelle1);

Min_Max_Jahr:

LOAD

Max(Jahr) as MaxJahr,

Min(Jahr) as MinJahr

Resident Delta;

Let MaxJahr = Peek('MaxJahr', -1);

Let MinJahr = Peek('MinJahr', -1);

For i = 1 to (NoOfRows('Delta')-1);

NoConcatenate

KG:

LOAD

Datum,

Jahr,

Feld1,

    Feld2,

    Feld3

Resident Delta where Jahr = $(vJahr);

Let vJahr = Peek('Jahr', -1,'KG');

Store KG INTO '$(vQVD)\Delta$(vJahr)'.qvd;

Next i;

Drop Table Delta;

Thank in advance

[el_aprendiz111 · Accepted solution]

Hi William

tmp:
LOAD*, YEAR(DATE(DATE#(Datum,'DD.MM.YYYY'),'DD/MM/YYYY')) AS YEAR;
LOAD * Inline
[
Datum,Feld1,Feld2,Feld3
01.01.2014,a,b,c
20.12.2015,b,d,e
04.11.2013,c,f,g
15.03.2012,ooo,fff,ieee
12.03.2012,d,h,i
15.08.2016,e,j,o
];
NoConcatenate

MxMn:
LOAD MAX(YEAR) AS MxYear,
MIN(YEAR) AS MnYar
Resident tmp;

LET vMxYear = Peek('MxYear');
LET vMnYar  = Peek('MnYar');
LET vName   = 'KG';

FOR i = $(vMnYar) to $(vMxYear)
sumary:
LOAD *, RowNo() AS ID Resident tmp Where YEAR = $(i);

STORE sumary into [Qvds\]$(vName)$(i).qvd (qvd);
DROP Table sumary;
NEXT

DROP Table MxMn,tmp;

[Anonymous]

Hi Fer Fer,

Thank you so much, it work perfect.