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: 
Not applicable

Cannot link my two tables properly

Hello,

I have written a script that makes an identifier by using the product of two nominal values. However, something seems to be going wrong and I don't know exactly what. Perhaps somebody can help me with my script.

The first part of my script loads a table with two lines per OTC-derivative position, each position having a unique Trade ID and a USI (unique swap identifier). However, some positions are external, which causes that we do not have the USI of these positions nor the external trade ID. By using the product of the 2 nominals I want to link them to another report that includes these external positions.

Firstly, I load the positions from a csv file. Then I use script to convert the table to a table where only one line remains per position, including the first nominal and the 2nd nominal. After having done that, I use the product of these 2 nominals to generate an identifier which I call [Identifier]

Then is when the trouble starts

I load a position report that should include the positions that I loaded firstly. This report has one line per position, with a nominal and a second nominal. I use the product of these nominals to generate the [Identifier] that I can use as a key field to link the two tables. However, I end up having a lot of rows containing the same values for all fields except for the identifier field, meaning I get a very large amount of of identifier fields that are linked to the same positions. Does anyone have a clue what might be the problem?

I attached my script to this post.

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

Many things can be wrong with this script. Especially when using numerical values as linkage fields.

In each output table, create a copy of the Identifier field with a unique name. For example:

Add [Nominal]*[NominalLeg2] AS [Identifier Hoofdtabel] to table Hoofdtabel (line 57) and

add [nominal1] * [Nominal2] AS [Identifier Positietabelfx] to table Positietabelfx (line 78)

Now create a table box with [Identifier Hoofdtabel], [Identifier] and [Identifier Positietabelfx] and compare the values that match/don't match. The association mismatches should be obvious.

Best,

Peter

View solution in original post

2 Replies
ramoncova06
Specialist III
Specialist III

this one of those cases where you really need to share the data in order for someone to able to provide a good advice

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Many things can be wrong with this script. Especially when using numerical values as linkage fields.

In each output table, create a copy of the Identifier field with a unique name. For example:

Add [Nominal]*[NominalLeg2] AS [Identifier Hoofdtabel] to table Hoofdtabel (line 57) and

add [nominal1] * [Nominal2] AS [Identifier Positietabelfx] to table Positietabelfx (line 78)

Now create a table box with [Identifier Hoofdtabel], [Identifier] and [Identifier Positietabelfx] and compare the values that match/don't match. The association mismatches should be obvious.

Best,

Peter