Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
i am trying to add rows to an existing qvd with concatenate. I want to check if there are allready rows with the same KPI, if yes i want the CommentNew of the most up to date row to be the CommentOld of the row i try to concatenate.
I only want to add rows to the qvd if the Value Old and Value New are different.
KPI | Value Old | Value New | CommentNew | CommentOld | Date | Header 7 |
---|---|---|---|---|---|---|
Bar | 1 | 2 | BLA | -- | 17.01.2016 | |
Bar | 2 | 3 | Hi | BLA |
In Script it would look like this:
Tmp:
Load
KPI,
Value Old,
Value New,
CommentNew,
CommentOld,
Date
from qvd.
concatenate (TMP)
Load
KPI,
Value Old,
Value New,
CommentNew,
//Pseudo Code
IF(KPI of the CSV = KPI of the Qvd, get me the latest CommentNew from Qvd,Null()) as CommentOld
Date
from csv
where ValueOld <> ValueNew
I hope its understandable.
My problem is that i cant access the column of the QVD Load.
Is there a way, how i can solve it?
Kr
Johann
The QVD loads as data table:
I know that the Qvd Files get loaded as a Table,
but i cant acces the column of the qvd table from another load of a different source:
Corr: //Load of the Correction QVD
LOAD
Key_Corr,
DatumKPI,
KPINameQVD,
Natco,
KPIValueOld,
KPIValueNew,
CommentSetOld,
CommentSetNew,
CommentDataNew,
CommentDataOld,
TimestampCorr
FROM $(Correction)Corrections.qvd(qvd);
Concatenate(Corr) // Attaching the rows where the values are Different to the Correction.qvd
Load
//RowNo() as ID,
Input_ID as Key_Corr,
DatumKPI,
Natco,
KPINameDaily as KPINameQVD,
KPIValue as KPIValueNew,
date#(FileTime ('$(vDataFile)'),'DD.MM.YYYY') as TimestampCorr,
KPIValueOld,
CommentDataNew,
Resident Daily
where ((KPIValue<>KPIValueOld) ) ;
i want to check on concatetante if there is allready a row with the same Input_ID, and if yes i want to add the CommentDataNew from the qvd to the row i want to concatenate.
I think something like this should work. Resorting to a JOIN is probably the easiest way of getting the newest date of a particular KPI when you need to match that
[Tmp]:
LOAD
KPI,
Date,
[Value Old],
[Value New],
CommentNew,
CommentOld
FROM
History.qvd (qvd);
[NewestCommentsPerKPI]:
LOAD
KPI,
Date(Max(Date)) AS OldDate,
FirstString(CommentNew) AS CommentNewHistory
RESIDENT
[Tmp]
GROUP BY
KPI;
RIGHT JOIN (NewestCommentsPerKPI)
CONCATENATE (Tmp) LOAD
KPI,
Date,
[Value Old],
[Value New],
CommentNew,
If( Not(IsNull(CommentNewHistory)) , CommentNewHistory , '' ) AS CommentOld
FROM
[Last.csv] (txt)
WHERE
ValueOld <> ValueNew;