topic Re: Concatenate // Get Values from Source Table in QlikView

Concatenate // Get Values from Source Table

johann_bauer — Thu, 12 Jan 2017 10:10:33 GMT

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?

Johann

Re: Concatenate // Get Values from Source Table

MindaugasBacius — Thu, 12 Jan 2017 10:14:52 GMT

The QVD loads as data table:

Re: Concatenate // Get Values from Source Table

johann_bauer — Thu, 12 Jan 2017 10:21:30 GMT

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.

Re: Concatenate // Get Values from Source Table

petter — Thu, 12 Jan 2017 11:15:42 GMT

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;