Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Cannot link my two tables properly

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

2 Replies
ramoncova06
Valued Contributor III

Re: Cannot link my two tables properly

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

Re: Cannot link my two tables properly

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