Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
matthewp
Creator III
Creator III

Union two tables not working

I have done this before with other QV solutions but this one isnt working.

I have 2 load scripts from 2 files (previous and current year)

2 qvds

1 qvw (front end)

Extract script 1 code (SQL is simplified for example) DiaryPY.qvw:

Diary:

Load * ;

SQL

SELECT * FROM Diary WHERE Year = '2017';

STORE Diary INTO C:Users\DiaryPY.QVD;

DROP TABLE Diary;

Extract script 2 code (SQL is simplified for example) Diary.qvw:

Diary:

Load * ;

SQL

SELECT * FROM Diary WHERE Year = '2018';

STORE Diary INTO C:Users\DiaryCY.QVD;

DROP TABLE Diary;


Load script 1 code (Qualifies are for other tabs that are working fine):

QUALIFY *;

UNQUALIFY Field1, Field2, Field3, Field4;

Diary:

LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\DiaryPY.QVD (qvd);


LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\DiaryCY.QVD (qvd);


The issue I am having is when i look at the Available fields loaded in, it is splitting them as Diary.Field1, Diary.Field2 etc and then DiaryCY.Field1, DiaryCY.Field2 etc.


I want them all to be recognised as the same table (like a UNION in SQL).

1 Solution

Accepted Solutions
matthewp
Creator III
Creator III
Author

For anyone who is having this problem, its is the UNQUALIFY it needs to be move to the bottom.

Diary:

LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\DiaryPY.QVD (qvd);


LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\DiaryCY.QVD (qvd);


QUALIFY *;

UNQUALIFY Field1, Field2, Field3, Field4;

View solution in original post

8 Replies
stabben23
Partner - Master
Partner - Master

Hi Matt,

use concatenate maybe?

QUALIFY *;

UNQUALIFY Field1, Field2, Field3, Field4;

Diary:

LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\DiaryPY.QVD (qvd);


CONCATENATE LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\DiaryCY.QVD (qvd);

matthewp
Creator III
Creator III
Author

Still the same issue

stabben23
Partner - Master
Partner - Master

try this then, the table Field will be used as a flag to keep them separated.

Diary:

LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8,

'PY' as table


FROM C:Users\DiaryPY.QVD (qvd);


CONCATENATE LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8,

'CY' as table


FROM C:Users\DiaryCY.QVD (qvd);

Peter_Cammaert
Partner - Champion III
Partner - Champion III

First merge the tables (using the CONCATENATE prefix that Staffan suggested), then Qualify the fields by loading them into a new Resident table..

Qualify will always er... qualify fields from different sources by tagging the field names.

matthewp
Creator III
Creator III
Author

Still the same issue

matthewp
Creator III
Creator III
Author

How can i do this?

juraj_misina
Luminary Alumni
Luminary Alumni

This is ineteresting, wouldn't expect this behaviour. I guess QUALIFY uses provided label and if no label provided then it tries to get label from source (hence Diary.Field5 but DiaryCY.Field5). I'd try two things:

//First option

QUALIFY *;

UNQUALIFY Field1, Field2, Field3, Field4;

Diary:

LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\Diary*.QVD (qvd);


//Second option

QUALIFY *;

QUALIFY *;

UNQUALIFY Field1, Field2, Field3, Field4;

Diary:

LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\DiaryPY.QVD (qvd);


Diary:

CONCATENATE (Diary) LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\DiaryCY.QVD (qvd);


matthewp
Creator III
Creator III
Author

For anyone who is having this problem, its is the UNQUALIFY it needs to be move to the bottom.

Diary:

LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\DiaryPY.QVD (qvd);


LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6,

Field7,

Field8


FROM C:Users\DiaryCY.QVD (qvd);


QUALIFY *;

UNQUALIFY Field1, Field2, Field3, Field4;