Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
You are looking for a SQL Query or do you want to do this in QlikView's script?
It'll be better to do in QlikView's script
Thanks.
Yes, it is)
Thanks)
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.
Thanks for your help.. It'll be useful for me in future