topic Re: Eliminating Synthetic Key in QlikView

Eliminating Synthetic Key

Wed, 25 Nov 2020 16:16:04 GMT

I have a unique table (Table A) of 5 columns with text strings, dates, and nulls. I want to link the text strings in Table A with the text strings in another table (Table B), but have not found a way to do so without creating a synthetic key (I put the Table A columns in Table B). I was wondering if there was a way to do this without having to do so. Table B contains only the text string data in the 5 columns in Table A, e.g.:

Table A: Table B:

Col 1 Col 2 Col 3 Col 4 Col 5 J

5/9 5/10 5/11 5/12 5/13 K

J M N O Q L

K P M

L N

5/16 5/17 5/18 5/19 5/20 Q

R S T R

U S

V T

W U

X V

I tried concatenating the two tables, but had no luck with eliminating the synthetic key. Would this have to be accomplished using a composite key? And if I have to do that, can I combine the auto number feature with a sql query?

Re: Eliminating Synthetic Key

santiago_respane — Tue, 10 May 2016 20:21:22 GMT

Hi Sean,

this can be solved, as you mentioned, by concatenating the two tables or with composite key.

Why did you get synthetic keys when concatenating?

Maybe you can share a little bit more info about the tables in your model, its kind of confusing the way they are now.

Kind regards,

Santiago

Re: Eliminating Synthetic Key

jonas_rezende — Wed, 11 May 2016 12:27:12 GMT

Hi, Sean Brophy.

The attached document, from slide 13 will help solve the problem. I'm not the author.

Hope this helps!

Re: Eliminating Synthetic Key

avinashelite — Wed, 11 May 2016 12:50:39 GMT

which is the key field in the Table A?? does all the columns have similar values ??

Re: Eliminating Synthetic Key

PrashantSangle — Wed, 11 May 2016 12:57:01 GMT

Hi,

go through below url

Synthetic Keys

How to remove synthetic key

https://community.qlik.com/message/72140#72140

Kind Regards

Re: Eliminating Synthetic Key

Wed, 11 May 2016 15:36:16 GMT

Hi Santiago - When I tried "forcing the relationship" between A and B, it created a Synthetic Table (I just combined all 8 fields into one table). Perhaps my data model below will add some color.

Avinash - all columns in Table A have a combination of dates, company names, and null values.

Table A is essentially a calendar, created via sql, with Companies scattered throughout, and Table B contains the details of each of these Companies. I understand most of what's been said above, but I can't simply concatenate the two tables in the load script, because of the unique nature of Table A. It's unique in the sense that it's essentially a calendar, and, using the example above, Company R is in no way related to Company S or Company T, but Qv assumes so as it's on the same record set row. I need the structure to be such that when the user filters on Company R, for example, Qv will associate, or 'pull', corresponding details for Company R that are in TableB, without associating anything that relates to Companies S or T (and the user would be able to see the associated data of R in a separate list box/input box with variables). Currently, I have variables set up in an input box so when the user filters Company R, an input box 'pulls in' details from Table B); everything is associating properly, but I thought it'd be best to eliminate the synthetic table that is enabling this relationship to occur. In my case here, is it best to leave the synthetic key/table as Max's links alluded to? My current data model is below. The 'ForceRelation' table idea was to simply extract just the Companies in Table A (so no dates, or nulls) into one column (to make it more of a 'normal' table structure), and naming it Comp1, Comp 2....Comp7. But I don't think this was the right idea.

I also did the same as above, but instead of duplicating the column 7 times, and just renaming it, I tried creating just one column with just the Companies from Table A (eliminating dates and nulls) as well as the Company column in Table B (so there'd be a one-to-one relationship in this new table), and then concatenating that table with Table A. This produced the following data model, but Qv would not associate the two fields (the calendar and the one with just the companies in a list) because, when clicking 'Preview', it essentially put the data from these new fields below the rows of the calendar (original Table A). So the new data was associated with null values in the original columns of the calendar.

Thank you in advance for any suggestions!!

Re: Eliminating Synthetic Key

avinashelite — Wed, 11 May 2016 16:18:30 GMT

try like this.

TABLE_A:

LOAD

[Col 1] &'-' &[Col 2]&'-' &[Col 3]&'-' &[Col 4]&'-' &[Col 5] as Key,

[Col 1],

[Col 2],

[Col 3],

[Col 4],

[Col 5]

from table;

TEMP:

LOAD Key,

[Col 1] as Key2,

KeepChar(Upper([Col 1]),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as T

resident

TABLE_A;

LOAD Key,

[Col 2] as Key2,

KeepChar(Upper([Col 2]),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as T

resident

TABLE_A;

LOAD Key,

[Col 3] as Key2,

KeepChar(Upper([Col 3]),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as T

resident

TABLE_A;

LOAD Key,

[Col 4] as Key2,

KeepChar(Upper([Col 4]),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as T

resident

TABLE_A;

LOAD Key,

[Col 5] as Key2,

KeepChar(Upper([Col 5]),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as T

resident

TABLE_A;

Link:

LOAD Key,

Key2

resident

TEMP

where len(trim(T))>0;

Drop table TEMP;

TABLE_B:

LOAD *,

Company as Key2

from table_b;

Re: Eliminating Synthetic Key

Thu, 12 May 2016 13:49:23 GMT

Avinash - thanks for your help; the data model looks great. However, now my variables are not being associated when filtering for a company on a row with multiple companies on it. Using the example above, if I were to filter on K, the variable doesn't produce a value because of the 'relationship' with P. However, if I were to select L, all the corresponding fields I assigned variables to would produce values. How can I get around this so my variable (which contains the field Company name) will produce one value?

Re: Eliminating Synthetic Key

avinashelite — Fri, 13 May 2016 03:50:17 GMT

whats the expression your using in variables ?? if you want to capture the current field selections try like this

=getfieldselections(filedname)

Re: Eliminating Synthetic Key

Fri, 13 May 2016 13:39:28 GMT

Just a field name in Table B. Even when I just create a simple list box of a field (company) in Table B, it associates everything that's on the same row as my selection in Table A (the calendar), and I can't 'isolate' what's being filtered on in Table A. I tried using getfieldselections(fieldname), but the same issue comes up. I think it's because the way the load script is set up: Table A's key, 'Key', concatenates each entire row in the calendar. And the Temp Table is trying to link these two Keys that aren't really the same. By that I mean, in regards to the above example, when clicking 'Preview' in the Tableviewer I see that where Key 2 = 'J', Key = 'J-M-N-O-Q'. So when I select 'J' in Table A, corresponding data for companies J, M, O, and Q all are 'white', or associated, in the list box. I just need a way to be able to have only J associated in the list box when I filter on J in Table A (I understand Table A/the calendar is a rather unusual table structure - I don't want data on the same row to be associated).

Re: Eliminating Synthetic Key

avinashelite — Fri, 13 May 2016 17:42:11 GMT

if you need like that, then you need to merge all the columns in Table A as single column like

LOAD [col 1] as Company

from

tableA;

LOAD [col 2] as Company

from

tableA;

etc

then you will get the perfect link , try this and let me know the results

Re: Eliminating Synthetic Key

Fri, 13 May 2016 18:04:28 GMT

And leave both Key and Key2? How can I create a relationship between the merged columns table and Tables A & B? I still need to present Table A in the dashboard as it is in the load script

Re: Eliminating Synthetic Key

avinashelite — Sat, 14 May 2016 18:28:23 GMT

Once you combine all the 5 columns , all the data will be merged in one column so finally in Table A you will have only one column..rename that column to Company i.e as you required in table B..Qlikview will link the tables only with common keys so it will automatically give you the desired results

Re: Eliminating Synthetic Key

Anonymous — Sun, 15 May 2016 05:29:49 GMT

it is useful to you

https://www.google.co.in/search?site=&source=hp&q=remove+synthetic+keys+qlikview&oq=remove+the+synth&gs_l=hp.1.0.0i22i30…

Re: Eliminating Synthetic Key

Tue, 17 May 2016 15:51:57 GMT

Understood - but I need a way to link the Merged Columns table and the original Table A, because I need to present Table A in the dashboard exactly as it is in the load script.

Re: Eliminating Synthetic Key

avinashelite — Tue, 17 May 2016 17:42:11 GMT

Then my first solution is better but you said no need to get linked with the other keys.......that's not possible in this case..OR you could add one extra condition in list box and other charts like

if(getselectedcount(company)>0 and getfieldselection(company)=field_name,field_name)