Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

k_dessing
New Contributor

Concatenate multiple dimension with Excel file

I am trying to link an excel file to a sql  database with concatenate in the script.

But a loop after reload makes synthetic key.

(Keys field are month and year and product code)

How can I avoid that

and just link without loop error?

1 Solution

Accepted Solutions

Re: Concatenate multiple dimension with Excel file

A better idea is to concatenate the two tables:

MyTable:

LOAD Month,Year,[Product Code], Amount, 'Direct_test' as Source From ...source1...;

Concatenate(MyTable)

LOAD Month,Year,[Product Code], Amount, 'Indirect_test' as Source From ...source2...;


If your second source is a database the you do the second load like this:


Concatenate(MyTable)

LOAD Month,Year,[Product Code], Amount, 'Direct_test' as Source;

SQL Select "Month","Year","Product Code", "Amount" FROM MySourceTable;


Your chart expressions then change to sum({<Source={'Direct_test'}>}Amount) and sum({<Source={'Indirect_test'}>}Amount) to make use of the Source field to determine which Amount values should be summed together.




talk is cheap, supply exceeds demand
7 Replies

Re: Concatenate multiple dimension with Excel file

Do you mean that your data model contains a loop? You need to resolve that by renaming one or more fields or concatenating some tables. If you need more help then we need more information. A small example qlikview document would be useful. Or at least the script you use to load the data.


talk is cheap, supply exceeds demand
k_dessing
New Contributor

Re: Concatenate multiple dimension with Excel file

Ths Gijsbert, appreciate it.

I'm trying to link an excel with Month and Year column and Product Portfolio Subfund Code.

Same dimensions exist in resident database.

Only, I getting a loop error even if I make a CONCATENATE declaration up front.

Synthetic keys are created.

See example:

Concatenate (Facts)

Load  [Product Portfolio Subfund Code],

          ...

          ...

          [Month],

          [Year]

FROM

[...source]

(ooxml, embedded labels, table is ...)


Re: Concatenate multiple dimension with Excel file

Ok, I'll try that one more time:

If you need more help then we need more information. A small example qlikview document would be useful. Or at least the script you use to load the data.

talk is cheap, supply exceeds demand
Highlighted
k_dessing
New Contributor

Re: Concatenate multiple dimension with Excel file

It is very difficult to send a file because the issue contains a database on the one hand and a excel file on the other hand.

Re: Concatenate multiple dimension with Excel file

Fair enough. Perhaps this document helps: Preparing examples for Upload - Reduction and Data Scrambling


talk is cheap, supply exceeds demand
k_dessing
New Contributor

Re: Concatenate multiple dimension with Excel file

Ok this is what I'm trying to establish.

However, this is with two excel files.

My problem is connecting an excel file with a database.

Pls se attached files

Re: Concatenate multiple dimension with Excel file

A better idea is to concatenate the two tables:

MyTable:

LOAD Month,Year,[Product Code], Amount, 'Direct_test' as Source From ...source1...;

Concatenate(MyTable)

LOAD Month,Year,[Product Code], Amount, 'Indirect_test' as Source From ...source2...;


If your second source is a database the you do the second load like this:


Concatenate(MyTable)

LOAD Month,Year,[Product Code], Amount, 'Direct_test' as Source;

SQL Select "Month","Year","Product Code", "Amount" FROM MySourceTable;


Your chart expressions then change to sum({<Source={'Direct_test'}>}Amount) and sum({<Source={'Indirect_test'}>}Amount) to make use of the Source field to determine which Amount values should be summed together.




talk is cheap, supply exceeds demand
Community Browser