topic Re: How to avoid multiple files being linked to a single value? in QlikView

How to avoid multiple files being linked to a single value?

Thu, 13 Oct 2016 12:51:31 GMT

Hi Qlik Community,

The exercise I am currently working on expects me to link stock mutations from certain dates to corresponding values from a different information file with the release date from the information file as close as possible to the stock mutation date. However, problems arises whenever more than one information file gets incorporated. This is because multiple information files refer to the same stock mutation. The stock mutation will not recognize the correct values it gets assigned as score. Instead, it reads multiple possible values and gives an error as output.

In short: Multiple information files refer to the same stock mutation, while only 1 information file can have the correct values.

Question: How to make sure not more than one information file gets assigned to a stock mutation?

I tried finding a similar question, but didn't succeed. I hope that anyone is familiar with my problem and is able to help me out. Thanks in advance.

Re: How to avoid multiple files being linked to a single value?

johnw — Thu, 13 Oct 2016 20:52:18 GMT

So I gather that currently, you have some key fields having to do with the stock mutation, and those key fields are being joined to multiple information files, each with a different release date.

One fix might be to go ahead and load and join with all the extra data you don't want. Then do an inner join at the end to keep only the row with the desired release date.

INNER JOIN ([My Table])
LOAD
[Key Field 1]
,[Key Field 2]
,max([Release Date]) as [Release Date]
RESIDENT [My Table]
WHERE [Release Date]<=[Mutation Date]
GROUP BY
[Key Field 1]
,[Key Field 2]
;

There may be more efficient or memory-conserving solutions.

Re: How to avoid multiple files being linked to a single value?

Fri, 14 Oct 2016 14:01:00 GMT

Hi John,

First of all I would like to thank you for your reply. Unfortunately, I have no access to Qlik the upcoming two days. I'll try to implement your suggestions on Sunday or Monday and let you know if I managed to succeed. Have a good weekend!

Regards,

Sebastian Heselaars