Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Hi Fer Fer,
Thank you so much, it work perfect.