New column based on two tables

[Apfelbaum]

Hello,

in my current data model, I have two tables connected by a key. Now, I want to porgram a "Status" field in the script based on values of the two tables. Basically the status is an if clause looking: if the keys of both tables match, if there is a key in table A but not in B, and if there is a key in table b but not in A. Basically, the status asks all of the conditions of a full outer joing (which Qlik performas automatically based on the same column name of the key). 

Because I am depended on the association of the two tables from Qlik first, I cant program the status in one of the LOAD statements of the tables (right?). Another approach which I pursuited is that I load the keys together in a seperate table and programmed the status field in there. Altough then, on my graphs and tables the values are displayed two times now. 

What is the best way to perform the status field I want to add? 

Thank you very much in advance. Cheers

[marcus_sommer]

I assume you need rather a change within your datamodel because with your approach you will either create two different status-fields or the status-field becomes a part of the key without really delivering your wanted information.

I could imagine that it would be more suitable if both tables - respectively the essential information - are merged, for example with a concatenate (union in SQL - joins are also thinkable but often more difficult).

Another thought is to adjust some parts within the UI to be able to differ between both sources, for example with something like:

count({<  UniqueFieldFromTable1 = {'*'}>} Key)
count({<  UniqueFieldFromTable2 = {'*'}>} Key)

- Marcus

[Apfelbaum]

Hey, 

thanks for the reply Marcus! 

Unfortunately, I cannot change the data sources. Basically, I have two different tables loaded in which I associate with a key. But table 1 contains some data which is not in table 2, and table 2 contains some data which is not contained in table 1. So basically, on the basis of the key Qlik creates a full outer join association. 

I could also manually join the two tables and then add the field. This works. But then I loose the overview in the table view and this is quite complicated to do with more than two tables.

Is there any way to add a column to the synthetic created overall table by Qlik, where I create a status variable based on the successful join of different tables? 
Basically an if-function with isNull(), but after the synthetic association of the keys by QlikView.

Hope you understand my problem and my question better with this explanation.

Laurin 

[marcus_sommer]

I'm not sure if I understand your third paragraph right but there is no overall table in Qlik else the datamodel is based on an associative approach and performed by creating proper keys - and here isn't just meant that the right field(s) are named equally else the content respectively the data-quality must be suitable, too.

In regard to your first and second paragraph I think both tables couldn't be directly joined - which means you will quite probably need to make more efforts by creating the datamodel and quite likely it would be sensible to change it.

The reason for it is that's not possible to link two tables directly if both sides have missing keys to the other side - regardless from which side you will look on the data they won't be complete. To solve such challenge there are at least three different approaches possible:

1. Comparing both side against each other to identify the missing keys and adding them as additionally records to the origin tables. After that you could join both tables or associate them in the datamodel.

2. Creating of a link-table which contains the distinct keys of both sides and which then connect both tables.

3. Just concatenating both tables respectively the essential parts.

The last one is usually the easiest way to handle such cases - which are quite often happens, nearly each datamodel which tries to match actual data with forecast data is affected. Personally I use this quite often and try to avoid link-tables which could become much larger as the origin tables and which are often quite (too) slow). The first method is mostly the "ideal" solution from a datamodel point of view but needs more efforts in developing it and also in the script run-times.

- Marcus

[Brett_Bleess]

If Marcus' last post gave you the info you needed to move forward, please be sure to close out your thread by using the Accept as Solution button on that post of his.  This gives him credit for the help and lets other Members know that worked.  If you are still working on things, please leave an update.  

The only additional thing I have is a link to the Design Blog area where there are mostly how-to posts, you might find some other ideas there, but one of which I was thinking was using a flag field to mark the records that had matches or did not such that you could use that flag field to do what you needed, I may be way off base though...

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett