Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johann_bauer
Partner - Contributor III
Partner - Contributor III

Concatenate // Get Values from Source Table

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.

KPIValue OldValue NewCommentNewCommentOldDateHeader 7
Bar12BLA--17.01.2016
Bar23HiBLA

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

3 Replies
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

The QVD loads as data table:

Screenshot_1.jpg

johann_bauer
Partner - Contributor III
Partner - Contributor III
Author

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.

petter
Partner - Champion III
Partner - Champion III

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;