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: 
Anonymous
Not applicable

SQL UNION query

Good day!

In sql data base i have view (Delivery_points_LastActualTeams) with such values

       

       

Point_idType1Type1NameType2Type2NameType3Type3Name
231Name1001Name3
45001Name21Name3
3200001Name3
561Name11Name200

Some of Point_id have more than one field (Type1 or Type2 or Type3) with value 1.

I need write sql query which will get me such table:

   

Point_idType(New Field)Name(New Field)
23Type1Name1
23Type3Name3
45Type2Name2
45Type3Name3
32Type3Name3
56Type1Name1
56Type2Name2
1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like this?

INPUT:

LOAD Point_id,

    Type1,

    Type1Name,

    Type2,

    Type2Name,

    Type3,

    Type3Name

FROM

[https://community.qlik.com/thread/171219]

(html, codepage is 1252, embedded labels, table is @1);

LOAD Point_id, 'Type1' as Type,Type1Name as Name

RESIDENT INPUT

WHERE Type1;

LOAD Point_id, 'Type2' as Type,Type2Name as Name

RESIDENT INPUT

WHERE Type2;

LOAD Point_id, 'Type3' as Type,Type3Name as Name

RESIDENT INPUT

WHERE Type3;

DROP TABLE INPUT;

View solution in original post

7 Replies
swuehl
MVP
MVP

Maybe like this?

INPUT:

LOAD Point_id,

    Type1,

    Type1Name,

    Type2,

    Type2Name,

    Type3,

    Type3Name

FROM

[https://community.qlik.com/thread/171219]

(html, codepage is 1252, embedded labels, table is @1);

LOAD Point_id, 'Type1' as Type,Type1Name as Name

RESIDENT INPUT

WHERE Type1;

LOAD Point_id, 'Type2' as Type,Type2Name as Name

RESIDENT INPUT

WHERE Type2;

LOAD Point_id, 'Type3' as Type,Type3Name as Name

RESIDENT INPUT

WHERE Type3;

DROP TABLE INPUT;

sunny_talwar

You are looking for a SQL Query or do you want to do this in QlikView's script?

Anonymous
Not applicable
Author

It'll be better to do in QlikView's script

Thanks.

sunny_talwar

swuehl‌ gave you the QlikView solution. That should work for you.

Best,

Sunny

Anonymous
Not applicable
Author

Yes, it is)

Thanks)

sinanozdemir
Specialist III
Specialist III

I know this question has been already answered, but the SQL code will look like the below:

Here is your dataset in SQL:

Capture.PNG

Here is the lengthy SQL code which will give the desired dataset:

Capture.PNG

If your Enterprise Data Warehouse (EDW) team keep adding more columns such as "Type4Name", "Type5Name", "Type4", or "Type5", in the SQL end, all you need to do is adding the column name that needs to be unpivotted. Once you bring this data model, the data will flow through your QlikView script. 

Anonymous
Not applicable
Author

Thanks for your help.. It'll be useful for me in future