topic Re: Union two tables not working in QlikView

Union two tables not working

matthewp — Mon, 26 Feb 2018 09:49:36 GMT

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).

Re: Union two tables not working

stabben23 — Mon, 26 Feb 2018 10:13:03 GMT

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);

Re: Union two tables not working

matthewp — Mon, 26 Feb 2018 10:28:34 GMT

Still the same issue

Re: Union two tables not working

stabben23 — Mon, 26 Feb 2018 11:05:42 GMT

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);

Re: Union two tables not working

Peter_Cammaert — Mon, 26 Feb 2018 11:07:36 GMT

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.

Re: Union two tables not working

matthewp — Mon, 26 Feb 2018 11:49:41 GMT

Still the same issue

Re: Union two tables not working

matthewp — Mon, 26 Feb 2018 11:50:03 GMT

How can i do this?

Re: Union two tables not working

juraj_misina — Mon, 26 Feb 2018 11:54:58 GMT

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 *;

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);

Re: Union two tables not working

matthewp — Mon, 26 Feb 2018 13:05:41 GMT

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;