topic Re: Concatenate in QlikView

Concatenate

heimo_ernst_wei — Tue, 23 Apr 2013 12:51:52 GMT

I have a problem which should be trivial but I did not find a solution. I try to add two (temporary) tables.

Sheet:

Load
SHEETID,
tmp_Sheet01.FileName as FileName,
tmp_Sheet01.Title as Title
Resident tmp_Sheet01;

DROP Table tmp_Sheet01;

//Concatenate (Sheet)

Load
SHEETID,
tmp_Sheet02.FileName as FileName,
tmp_Sheet02.Title as Title
Resident tmp_Sheet02;

DROP Table tmp_Sheet02;

The result is (with and without the "concatenate") that I have finally still two tables: "Sheet" and "Sheet-1"

Might looking stupid but I'm finding the error....

Thanks for any help

Re: Concatenate

Tue, 23 Apr 2013 12:54:16 GMT

Load
SHEETID,
[tmp_Sheet01.FileName] as FileName,
[tmp_Sheet01.Title] as Title
Resident tmp_Sheet01;

//Concatenate (Sheet)

Load
SHEETID,
[tmp_Sheet02.FileName] as FileName,
[tmp_Sheet02.Title] as Title
Resident tmp_Sheet02;

DROP Table tmp_Sheet01;

DROP Table tmp_Sheet02;

Re: Concatenate

heimo_ernst_wei — Tue, 23 Apr 2013 13:27:06 GMT

Thanks for your proposals

However it did not work.

What I get now is:

without concatenate statement:

Two tables one "Sheet" th other "tmp-Sheet02-1"

With concatement statement:

One table "Sheet" with five columns:

Sheet.FileName
Sheet.Title
SHEETID
tmp-Sheet02-1.FileName
tmp-Sheet02-1.Title

The same applies using "ABCD" instead of "Sheet".

ABCD:

Load
SHEETID,
[tmp_Sheet01.FileName] as FileName,
[tmp_Sheet01.Title] as Title
Resident tmp_Sheet01;

Concatenate (ABCD)

Load
SHEETID,
[tmp_Sheet02.FileName] as FileName,
[tmp_Sheet02.Title] as Title
Resident tmp_Sheet02;

DROP Table tmp_Sheet01;

DROP Table tmp_Sheet02;

Result:

One table "ABCD" with five columns:

ABCD.FileName
ABCD.Title
SHEETID
tmp-Sheet02-1.FileName
tmp-Sheet02-1.Title

Re: Concatenate

Tue, 23 Apr 2013 13:32:16 GMT

try this

QUALIFY FileName,Title;

ABCD:

Re: Concatenate

Anonymous — Tue, 23 Apr 2013 13:34:12 GMT

Looks like have QUALIFY statement before all that (?)

Re: Concatenate

Tue, 23 Apr 2013 13:41:14 GMT

Can you post your work file ?

Re: Concatenate

er_mohit — Tue, 23 Apr 2013 13:44:28 GMT

try this

sorry something error in that dont put the * by mistake

qualify FileName,Title;

ABCD:

Result:

One table "ABCD" with five columns:

ABCD.FileName
ABCD.Title
SHEETID
tmp-Sheet02-1.FileName
tmp-Sheet02-1.Title

Re: Concatenate

Anonymous — Tue, 23 Apr 2013 13:47:54 GMT

"Qualify" is the likley reason of the problem. The original script (the one in opening of the thread) should work if preceeded with

UNQUALIFY *;

Re: Concatenate

Tue, 23 Apr 2013 13:48:32 GMT

QUALIFY FileName,Title;

ABCD:

Load
SHEETID,
[tmp_Sheet01.FileName] as FileName,
[tmp_Sheet01.Title] as Title
Resident tmp_Sheet01;

Concatenate (ABCD)

Load
SHEETID,
[tmp_Sheet02.FileName] as FileName,
[tmp_Sheet02.Title] as Title
Resident tmp_Sheet02;

DROP Table tmp_Sheet01;

DROP Table tmp_Sheet02;

Re: Concatenate

heimo_ernst_wei — Tue, 23 Apr 2013 13:57:18 GMT

What I have before is:

QUALIFY * ;
UNQUALIFY SHEETID;

Regards Heimo

Re: Concatenate

Anonymous — Tue, 23 Apr 2013 14:00:22 GMT

This is exactly the reason of your problem. All your fields except SHEETID have different names, hence the table do not auto-concatnate. And if you force to concatenate, they have different names in the resulting table.

Re: Concatenate

heimo_ernst_wei — Tue, 23 Apr 2013 14:01:42 GMT

Qualify [tmp_Sheet01.FileName],[tmp_Sheet01.Title],[tmp_Sheet02.FileName],[tmp_Sheet02.Title];

Does not solve the problem.

Regards Heimo

Re: Concatenate

heimo_ernst_wei — Tue, 23 Apr 2013 14:04:11 GMT

loding with "Load abcd AS xxxx" results in equal column names (xxxx) - at least I thought this.

Re: Concatenate

heimo_ernst_wei — Tue, 23 Apr 2013 14:05:38 GMT

adding UNQUALIFY *;

This solved my problem!

Thanks!

UNQUALIFY *;

Sheet:

Load

SHEETID,

[tmp_Sheet01.FileName] as FileName,

[tmp_Sheet01.Title] as Title,

[tmp_Sheet01.%Key_DocumentSummary_3E3ED09C8BC69525]

Resident tmp_Sheet01;

Concatenate (Sheet)

Load

SHEETID,

[tmp_Sheet02.FileName] as FileName,

[tmp_Sheet02.Title] as Title,

[tmp_Sheet02.%Key_DocumentSummary_3E3ED09C8BC69525]

Resident tmp_Sheet02;

DROP Table tmp_Sheet01;

DROP Table tmp_Sheet02;

Re: Concatenate

Anonymous — Tue, 23 Apr 2013 14:09:39 GMT

That means that the ABCD was auto-concatenated to a previous table which was later dropped.

Use NOCONCATENATE when loading ABCD.

ABCD:

NOCONCATENATE

LOAD

...

CONCATENATE (ABCD) LOAD

....