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

Match to second field, if first field is empty?

Hi,

I have a table, which have two key values, which are connected to the other tables in the docuement (Sales Area, Subsidiarys).
(i.e.) i have a row, which looks like the following:

ID Name Sales Area Subsidiary

1 Bill Paxton Germany Munich
2 Jake Newton England

When I'm selecting ID 1 QlikView matches the data related to "Munich" - so QlikView first match the Subsidiary column.
Now I want to select ID 2. Here, there's no entry in Subsidiary, so QlikView should match the data from "England" - the Sales Area column.
But when I'm selecting ID 2, QlikView doesn't show up any data.

I added the part of the picture from the table viewer, which shows, that the two columns of the table are connected to the other tables correctly.

Any ideas how to match on Sales Area, if no entry in Subsidiary exists?

Thanks for help!

error loading image

1 Solution

Accepted Solutions
Not applicable
Author

Hi HofbauerAn,

You want me to explain the solution part right. OK... What I said is, if you have a table like this ie.

load * inline

[

Countries, Subsidiary1

Germany, Munich

Germany, XYZ

England, Manchester

];

This is a mapping table with your original table and the key field here is the Country field. Then instead of using Subsidary in you chart use the presently created Subsidiary1 so that whenever a country is seleceted or country is given in the dimension, it gets its mpping subsidiary location from the inline table we have created. I hope what I am trying to say is clear.

Regarding the synthetic keys, please go through in the reference manual. It is explained well there on what synthetic keys are and how to solve the synthetic keys.

Hope this helps you. If you want any more help please ask.

Thanks Joseph......

View solution in original post

8 Replies
Not applicable
Author

No Ideas?
I really need help with this.

Thanks.

Not applicable
Author

Hi Hofbauren,

Sometimes if there are synthetic keys in your table then the data may not match with each other or you wont be able to get the correct results you are looking for. so what you have to do is remove the synthetic key first. Even if this is changed, you wont be getting a solution for the problem you have specified here. What I suggest is that, if you have a mapping table to map different countries and its corresponding subsidiaries, you will get a solution. You can just add an inline table having countries and its coerresponding subsidiaries and then look for a solution. hope this helps you.....

Thanks Joseph..........

Not applicable
Author

Hi Thopz,

thanks for your answer.
Can you please explain your suggestions of how I can solve my problem a little further.
Would be great, because I'm not really expirienced with QlikView.


Thank you very much.

Not applicable
Author

Hi HofbauerAn,

You want me to explain the solution part right. OK... What I said is, if you have a table like this ie.

load * inline

[

Countries, Subsidiary1

Germany, Munich

Germany, XYZ

England, Manchester

];

This is a mapping table with your original table and the key field here is the Country field. Then instead of using Subsidary in you chart use the presently created Subsidiary1 so that whenever a country is seleceted or country is given in the dimension, it gets its mpping subsidiary location from the inline table we have created. I hope what I am trying to say is clear.

Regarding the synthetic keys, please go through in the reference manual. It is explained well there on what synthetic keys are and how to solve the synthetic keys.

Hope this helps you. If you want any more help please ask.

Thanks Joseph......

Not applicable
Author

Hi Thopz,

thanks for your help.
Ok, i need to create an "Inline Table" .

So in my situation the table looks the following:

LOAD * INLINE
[
Sales Area, Subsidiary1 // I dont know exactly for what the 1 have to be here
CISMAT, CIS
CISMAT, ME
Southern Europe, TIO
Central Europe, TSA
Central Europe, TGA
]
FROM TABLE 1;

So, when I'm understanding right, with an Inline table I can relate each Subsidiary to the right Sales Area and I have to write each relation for each Subsidiary.

What is the exactly syntax for the INLINE Load. I get an error, when trying to load it in the way explained above.

Maybe I'm on the totally wrong way...

Thank you very much for your help in this case.

Not applicable
Author

Hi,

what you have written is correct except the last line. you dont have to specify the from clause here. just fter the cloasing bracket ] put a semicolon to end it as shown in the code given below.Inline tables are not derived from any tables. they are eindividual tables of its own. so the exact syntax is like this

load * inline

[

country, subsidiary

india, xyz

england, abc

];

Once you create this, an automatic link will be vreated by qlikview with the countries field in your original table. The thing is you have to give the same name tht is 'country' in the mapping table as it is in your original table.Hope this helps you!!

Thanks Joseph.........

Not applicable
Author

Cant get it to work with my QlikView File 😞

I created the Inline Table but I cant get rid of the synthetic key.
So, when I select an ID, which havent an entry in the column Subsidiary, QlikView don't show anything.

It would be great, if you can create a short "Demo-QlikView-File" for me..

Sorry for the inconvenience.

Thank you very much.

Not applicable
Author

It works now!

Thanks for your help 🙂