Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

andreyfcdk91
Contributor

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
MVP
MVP

Re: SQL UNION query

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;

7 Replies
MVP
MVP

Re: SQL UNION query

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

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

andreyfcdk91
Contributor

Re: SQL UNION query

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

Thanks.

Re: SQL UNION query

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

Best,

Sunny

andreyfcdk91
Contributor

Re: SQL UNION query

Yes, it is)

Thanks)

sinanozdemir
Valued Contributor III

Re: SQL UNION query

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. 

andreyfcdk91
Contributor

Re: SQL UNION query

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