topic Re: QlikView Concatenation in Connectivity & Data Prep

QlikView Concatenation

danm84306-2 — Thu, 28 Jul 2022 15:40:37 GMT

Hello,

I am looking to append two tables (with the same field names) as one table. I used Concatenate with and without Residents and I still get the same thing...3 rows in each table now turn into six rows. Nothing I have tried worked (I tried every type of join as well)

Test1 Data

Field1	Field2	Field3
R1F1	R1F2	R1F3
R2F1	R2F2	R2F3
R3F1	R3F2	R3F3

Test2 Data

Field1	Field2	Field3
R4F1	R4F2	R4F3
R5F1	R5F2	R5F3
R6F1	R6F2	R6F3

I want

Field1	Field2	Field3
R1F1	R1F2	R1F3
R2F1	R2F2	R2F3
R3F1	R3F2	R3F3
R4F1	R4F2	R4F3
R5F1	R5F2	R5F3
R6F1	R6F2	R6F3

Re: QlikView Concatenation

Or — Thu, 28 Jul 2022 15:54:06 GMT

This seems to work fine with a simple concatenate...

Load * INLINE
[
Field1, Field2, Field3
R1F1, R1F2, R1F3
R2F1, R2F2, R2F3
R3F1, R3F2, R3F3];
CONCATENATE Load * INLINE [
Field1, Field2, Field3
R4F1, R4F2, R4F3
R5F1, R5F2, R5F3
R6F1, R6F2, R6F3];

If you're not getting the expected results, I'd suggest checking that the field names are indeed identical (including case, spacing, etc). Note that this is still six rows, because it's supposed to be six rows (three from each table).

Re: QlikView Concatenation

danm84306-2 — Thu, 28 Jul 2022 15:58:21 GMT

Thank you, I did check this as well. I am doing this as a test with Excel files because I am trying to do this with QVDs and I'm getting the same results. I figured I can test this out to get the syntax down right then apply to my QVW with real data

Re: QlikView Concatenation

danm84306-2 — Thu, 28 Jul 2022 16:04:10 GMT

Script

Test1:
LOAD Field1,
Field2,
Field3
FROM
[D:\Qlikview\Development\Test1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate(Test1)

LOAD Field1,
Field2,
Field3
FROM
[D:\Qlikview\Development\Test2.xlsx]
(ooxml, embedded labels, table is Sheet1);

Result:

Sheet1.Field1	Sheet1.Field2	Sheet1.Field3	Test1.Field1	Test1.Field2	Test1.Field3
R4F1	R4F2	R4F3
R5F1	R5F2	R5F3
R6F1	R6F2	R6F3
			R1F1	R1F2	R1F3
			R2F1	R2F2	R2F3
			R3F1	R3F2	R3F3

Re: QlikView Concatenation

danm84306-2 — Thu, 28 Jul 2022 16:05:52 GMT

Script with Resident

Test1:
LOAD Field1,
Field2,
Field3
FROM
[D:\Qlikview\Development\Test1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Test2:
LOAD Field1,
Field2,
Field3
FROM
[D:\Qlikview\Development\Test2.xlsx]
(ooxml, embedded labels, table is Sheet1);

Test3:

NoConcatenate

LOAD *

Resident Test1;

Concatenate(Test3)

LOAD *

Resident Test2;

Drop Tables Test2, Test1;

Result:

Test2-1.Test2.Field1	Test2-1.Test2.Field2	Test2-1.Test2.Field3	Test3.Test1.Field1	Test3.Test1.Field2	Test3.Test1.Field3
R4F1	R4F2	R4F3
R5F1	R5F2	R5F3
R6F1	R6F2	R6F3
			R1F1	R1F2	R1F3
			R2F1	R2F2	R2F3
			R3F1	R3F2	R3F3

Re: QlikView Concatenation

Or — Thu, 28 Jul 2022 16:20:32 GMT

An earlier part of your script has Qualify *; in it, seemingly. Either remove that, or use Unqualify *; before these statements to disable it for this part.

Re: QlikView Concatenation

danm84306-2 — Thu, 28 Jul 2022 16:28:35 GMT

I commented out QUALIFY

Results

Sheet1.Field1	Sheet1.Field2	Sheet1.Field3	Test1.Field1	Test1.Field2	Test1.Field3
R4F1	R4F2	R4F3
R5F1	R5F2	R5F3
R6F1	R6F2	R6F3
			R1F1	R1F2	R1F3
			R2F1	R2F2	R2F3
			R3F1	R3F2	R3F3

Re: QlikView Concatenation

danm84306-2 — Thu, 28 Jul 2022 16:30:08 GMT

Here are the two test files, If anyone can create a test QVW with a script that works, please let me know