Eliminating Synthetic Key

[#1]

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

                                                                                                            O

                                                                                                            P

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

R            S                                      T                                                   R

                                                       U                                                  S

                                                       V                                                  T

                                                       W                                                 U

                                                       X                                                  V

                                                                                                           W

                                                                                                           X

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?

[santiago_respane]

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

[jonas_rezende]

Hi, Sean Brophy.

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

Hope this helps!

[avinashelite]

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

[PrashantSangle]

Hi,

go through below url

Synthetic Keys

How to remove synthetic key

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

Kind Regards

[#6]

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!!

[avinashelite]

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;

[#8]

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?

[avinashelite]

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

=getfieldselections(filedname)

[#10]

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).