Association Problem with the Wizard

[#1]

Hi

I'm not a programmer - but rather, I am trying to use the wizard to load data from 2 excel files.

I've created two files that have two columns in common (with the same name) and was expecting Qliksense to associate them, but alas, it didn't. This is what I got:

[Calls]:

LOAD [Call],

  [Call Date],

  [Customer No],

  [Sort Name],

  [Call End User],

  [EU Name],

  [Line],

  [Item],

  [Item Description],

  [Serial],

  [Rev in Call],

  [Status],

  [Call Description],

  [Problem],

  [Problem Desc],

  [Cause],

  [Cause Desc],

  [Resolution],

  [Resolution Desc],

  [index]

FROM [lib://AttachedFiles/Calls.xlsx]

(ooxml, embedded labels, table is Calls);

[ISB]:

LOAD

  [Serial] AS [ISB.Serial],

  [Item] AS [ISB.Item],

  [ISB Rev],

  [Item Type],

  Date(Date#([Install Date], 'M/D/YYYY') ) AS [Install Date],

  [Warranty Code],

  [Warranty Expiration],

  [Days Left for Warr],

  [ISB End User]

FROM [lib://AttachedFiles/ISB.xlsx]

(ooxml, embedded labels, table is ISB);

And the autocalendar was created as well.

As you can see - Items and Serial are both written exactly the same - why won't it associate it?!

[sunny_talwar]

Because you aliased them in your second load... try this

[Calls]:

LOAD [Call],

  [Call Date],

  [Customer No],

  [Sort Name],

  [Call End User],

  [EU Name],

  [Line],

  [Item],

  [Item Description],

  [Serial],

  [Rev in Call],

  [Status],

  [Call Description],

  [Problem],

  [Problem Desc],

  [Cause],

  [Cause Desc],

  [Resolution],

  [Resolution Desc],

  [index]

FROM [lib://AttachedFiles/Calls.xlsx]

(ooxml, embedded labels, table is Calls);

[ISB]:

LOAD

[Serial],

  [Item],

  [ISB Rev],

  [Item Type],

  Date(Date#([Install Date], 'M/D/YYYY') ) AS [Install Date],

  [Warranty Code],

  [Warranty Expiration],

  [Days Left for Warr],

  [ISB End User]

FROM [lib://AttachedFiles/ISB.xlsx]

(ooxml, embedded labels, table is ISB);

UPDATE: QlikView or Qlik Sense make association based on same field names. If you change the name of the field, then Qlik Sense won't know that these two fields need to be associated with one another

[#3]

Sunny, thank you, but I didn't do anything - it was auto generated by the application.

I just copied it for the purpose of asking the question.

Since it is created Automatically - I have to unlock to change manually and I wonder if it's the right thing to do...?

Anyway - just for the sake of testing I did and and it looks like it created a syn key - so I assume that's what I needed, right?

[sunny_talwar · Accepted solution]

Well, I don't know why auto-generated script renamed it, but I think there should be no harm in un-locking the script (unless I am missing something since I don't have too much knowledge around Qlik Sense's auto-generated script). But in order to avoid synthetic key, you can try this

[Calls]:

LOAD AutoNumber(Serial&Item) as Key,

  [Call],

  [Call Date],

  [Customer No],

  [Sort Name],

  [Call End User],

  [EU Name],

  [Line],

  [Item],

  [Item Description],

  [Serial],

  [Rev in Call],

  [Status],

  [Call Description],

  [Problem],

  [Problem Desc],

  [Cause],

  [Cause Desc],

  [Resolution],

  [Resolution Desc],

  [index]

FROM [lib://AttachedFiles/Calls.xlsx]

(ooxml, embedded labels, table is Calls);

[ISB]:

LOAD AutoNumber(Serial&Item) as Key,

[Serial] AS [ISB.Serial],

  [Item] AS [ISB.Item],

  [ISB Rev],

  [Item Type],

  Date(Date#([Install Date], 'M/D/YYYY') ) AS [Install Date],

  [Warranty Code],

  [Warranty Expiration],

  [Days Left for Warr],

  [ISB End User]

FROM [lib://AttachedFiles/ISB.xlsx]

(ooxml, embedded labels, table is ISB);

So, basically you can combine Serial and Item to create a new field and let the association take place on that field and simultaneously keep the renaming of Serial and Item fields within the ISB table.

[#5]

Thank you! it looks great.