topic SQL UNION query in QlikView

SQL UNION query

Anonymous — Sat, 04 Jul 2015 12:37:47 GMT

Good day!

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

Point_id	Type1	Type1Name	Type2	Type2Name	Type3	Type3Name
23	1	Name1	0	0	1	Name3
45	0	0	1	Name2	1	Name3
32	0	0	0	0	1	Name3
56	1	Name1	1	Name2	0	0

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_id	Type(New Field)	Name(New Field)
23	Type1	Name1
23	Type3	Name3
45	Type2	Name2
45	Type3	Name3
32	Type3	Name3
56	Type1	Name1
56	Type2	Name2

Re: SQL UNION query

swuehl — Sat, 04 Jul 2015 12:47:31 GMT

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;

Re: SQL UNION query

sunny_talwar — Sat, 04 Jul 2015 12:48:24 GMT

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

Re: SQL UNION query

Anonymous — Sat, 04 Jul 2015 12:49:32 GMT

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

Thanks.

Re: SQL UNION query

sunny_talwar — Sat, 04 Jul 2015 12:51:07 GMT

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

Best,

Sunny

Re: SQL UNION query

Anonymous — Sat, 04 Jul 2015 13:10:50 GMT

Yes, it is)

Thanks)

Re: SQL UNION query

sinanozdemir — Sat, 04 Jul 2015 18:33:09 GMT

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

Here is your dataset in SQL:

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

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.

Re: SQL UNION query

Anonymous — Sat, 04 Jul 2015 18:37:38 GMT

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