Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Highlighted
Not applicable

Association Problem with the Wizard

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

1 Solution

Accepted Solutions

Re: Association Problem with the Wizard

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.

4 Replies

Re: Association Problem with the Wizard

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

Not applicable

Re: Association Problem with the Wizard

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?

Re: Association Problem with the Wizard

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.

Not applicable

Re: Association Problem with the Wizard

Thank you! it looks great.