topic Re: How to avoid automatic concatenation in QlikView

How to avoid automatic concatenation

Thu, 02 Feb 2012 12:08:35 GMT

Hello,

I've got an Excel-file with multiple sheets. Each sheet contains the same set of fields and even partially have the same data. I added an example to clarify the situation: Suppose each sheet represents the results of a certain month (sheetname: Y11M01 for January 2011) for a fixed set of variables (column A in each sheet).

I created a grahical view for 1 month to start, now want to expand the dataset to the whole year.

When I load the different sheets in Qlikview, they automatically concatenate (attachment: Screenshot.jpg) when uploaded. I get no error messages but the graphical result is completely messed up.

How can I upload these different sheets so I can create grahics on results, calculations,.. on the data available in the three sheets (separately or combined) or how to program so there considered to be three different linked tables.

Thanks in advance for the suggestions,

Stijn

How to avoid automatic concatenation

Thu, 02 Feb 2012 12:14:00 GMT

You can prevent the automatic concatenation with the noconcatenate prefix.

How to avoid automatic concatenation

Thu, 02 Feb 2012 12:18:13 GMT

Stijn,

Take a look at the 'Qualify' statement which adds the table name to the field names. Then add NOCONCATENATE to the LOAD.

You could make the name of the table as the sheetname to help identify better.

The only downside to this is that you will have to change the field labels for the UI.

Regards,

Gordon

Re: How to avoid automatic concatenation

hic — Thu, 02 Feb 2012 12:18:48 GMT

True, but I think you should concatenate them:

for each vMonth in 'Y11M01', 'Y11M02', 'Y11M03'
LOAD
'$(vMonth)' as Month,
ID,
[Product 1],
[Product 2],
[Product 3]
FROM [Example_file.xls] (biff, embedded labels, table is $(vMonth)$);
Next vMonth

How to avoid automatic concatenation

Thu, 02 Feb 2012 12:38:05 GMT

Hello Henric,

thanks a lot for this simple solution ! It works perfectly.

Kind regards,

Stijn

How to avoid automatic concatenation

Thu, 02 Feb 2012 12:40:26 GMT

Hello Gordon,

thanks for the response. I tried it but the software crashed for unknown reasons. The solution suggested by Henric (see below) gives the same result without the unexpected crash !

Still thanks for the quick and correct response !

Stijn

Re: How to avoid automatic concatenation

satyansss — Sat, 24 Feb 2018 21:17:06 GMT

Awesome!!!!!! Henric