Union two tables not working

[matthewp]

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

[stabben23]

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]

Still the same issue

[stabben23]

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]

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]

Still the same issue

[matthewp]

How can i do this?

[juraj_misina]

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 · Accepted solution]

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;