Merging rows + adding new column

Tue, 28 Apr 2015 13:27:05 GMT

Hi all,

Thanks to the help of a few forum members I have been able to build a Qlikview dashboard based on data from our administration and several external feedback reports. However, linking the data to the appropriate feedback is not always possible via my key value (identifier). Therefore, I need to create an identifier, using the product of the nominal values of the position that I am after. Each position has two nominals (near leg and far leg of the trade).

I cannot share my data due to its confidential nature. However, for the sake of clarity, I have made an illustrative table that would describe my situation.

Trade Data:

Trade Number Nominal Trade Date

4230 50398,12 12-04-2015

4230 70956,32 12-04-2015

4256 30457,67 14-04-2013

4256 48589,02 14-04-2013

Feedback Report:

Identifier Nominal 1 Nominal 2 Trade Date Status

N/A 50398,12 70956,32 12-04-2015 Pending

N/A 30457,67 48589,02 14-04-2013 Matched

What I would actually like is to

1) Merge the rows of each position so that one row is left for each position. All data except for the nominal is the same. Therefore, I would want to use the data of the first row that is encountered and add a column (nominal 2) containing the nominal of the second line. This code should be leading to a table that would look like the data of the feedback report, meaning that one row per Trade Number remains, containing both nominals.

2) After that it would be easy to create an identifier that should be unique by loading a new variable which is the product of both nominals + the trade date + maturity date, which is likely to be a unique identifier. I can then use that identifier to link the trades to the positions in the feedback report that do not contain an identifier (our trade number).

Number 1 is where I am currently stuck, if I get that fixed number 2 should be a no-brainer. If anyone can help me it would be greatly appreciated.

Re: Merging rows + adding new column

sunny_talwar — Tue, 28 Apr 2015 13:37:01 GMT

May be something like this:

Table:

LOAD [Trade Date],

FirstValue(Nominal) as Nominal1,

[Trade Date]&FirstValue(Nominal) as Check

Resident PreviousTable

Group By [Trade Date];

Join (Table)

LOAD [Trade Date],

Nominal as Nominal2

Resident PreviousTable

Where not Exist(Check, [Trade Date]&Nominal);

Drop Field Check;

Re: Merging rows + adding new column

gladi-cz — Tue, 28 Apr 2015 13:58:14 GMT

Hi Martijin,

may be help you CROSSTABLE command in script. Could you share your qvf file?

Re: Merging rows + adding new column

sunny_talwar — Tue, 28 Apr 2015 13:59:53 GMT

Did not know that this was QlikSense question. May or may not work.

Best,

Sunny

Re: Merging rows + adding new column

Tue, 28 Apr 2015 14:23:02 GMT

Sure, please see the attached file.

topic Merging rows + adding new column in QlikView

Merging rows + adding new column

Re: Merging rows + adding new column

Re: Merging rows + adding new column

Re: Merging rows + adding new column

Re: Merging rows + adding new column