Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Hi Martijin,
may be help you CROSSTABLE command in script. Could you share your qvf file?
Did not know that this was QlikSense question. May or may not work.
Best,
Sunny
Sure, please see the attached file.