Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
mkudis
Contributor II
Contributor II

Formatting Data from column to row Qlikview

I am importing data from my ERP where the data is not very usuable in Qlik for my needs.  It comes in as the below:

Item Code, Item Description, Label Name, Label Value

ItemABC, ABCdescription, Color Family, Brown

ItemABC, ABCdescription, Product Type, Siding

ItemABC, ABCdescription, Style, Trim

ItemXYZ, XYZdescription, Color Family, White

ItemXYZ, XYZdescription, Product Type, Siding

ItemXYZ, XYZdescription, Style, Vertical Siding

ItemDEF, DEFdescription, Color Family, Black

ItemDEF, DEFdescription, Product Type, Trim

ItemDEF, DEFdescription, Style, Trim

 

What I ultimately want to do is create a table with the dimension Item Code and Item Description, and the expression be multiple columns for each of the different labelnames (Color Family, Product Type, Style, etc) and the label values will fall where they should under that expression.  

Basically what I'd like to see is:

Item Code,  Item Description,  Color Family,  Product Type,  Style

ItemABC, ABCdescription, Brown, Siding, Trim

ItemXYZ, XYZdescription, White, Siding, Vertical Siding

ItemDEF, DEFdescription, Black, Trim, Trim

 

How can I do this, whether in the load script or utilizing expressions in sheet objects?

Labels (2)
1 Solution

Accepted Solutions
mkudis
Contributor II
Contributor II
Author

I was able to produce my desired result by creating separate tables dependent on the Label Name with If statements.  It will take some maintenance if we add new label names at some point, but it is now working exactly as I have envisioned.  It created the pinwheel data model similar to what the generic load should, probably not the most desirable solution but it works. DataModel.JPG

View solution in original post

6 Replies
Vegar
MVP
MVP

You can achieve this by using Generic Load. Try the script below. 


Generic LOAD * inline [
Item Code, Item Description, Label Name, Label Value
ItemABC, ABCdescription, Color Family, Brown
ItemABC, ABCdescription, Product Type, Siding
ItemABC, ABCdescription, Style, Trim
ItemXYZ, XYZdescription, Color Family, White
ItemXYZ, XYZdescription, Product Type, Siding
ItemXYZ, XYZdescription, Style, Vertical Siding
ItemDEF, DEFdescription, Color Family, Black
ItemDEF, DEFdescription, Product Type, Trim
ItemDEF, DEFdescription, Style, Trim];

 

This will leave you with a data model that looks like a fan. You might want to tidy up by joining the Label-Value tables into a single wide table. Henric Cronströms blog post on the topic, The Generic Load, could be a good read.

Good luck!

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
mkudis
Contributor II
Contributor II
Author

I am getting an error that Generic tables must contain at least 3 fields.  Not sure why I am getting that error, my table has four fields.  I am connected to my ERP so I'm not loading the actual data as you show.

 

Item_attributes:
GENERIC LOAD * INLINE [
item_ptr,
groupname,
labelname,
labelvalue
];

SQL SELECT
attr_item.item_ptr,
attr_group.description as 'groupname',
attr_label.name as 'labelname',
attr_valdtl.label_value as 'labelvalue'

FROM
PUB.attr_item
LEFT OUTER JOIN pub.attr_group on
attr_group.system_id = attr_item.group_id_sysid and
attr_group.group_id = attr_item.group_id
LEFT OUTER JOIN pub.attr_valdtl on
attr_valdtl.system_id = attr_item.value_id_sysid and
attr_valdtl.value_id = attr_item.value_id
LEFT OUTER JOIN pub.attr_label on
attr_label.label_id = attr_valdtl.label_id

WHERE attr_item.attr_type = 'I'
;

Vegar
MVP
MVP

Your Item_attributes table contains one field with three values.

item_ptr
groupname
labelname
labelvalue

 

Try this inline code instead. It will generate a four column table with no rows.

Item_attributes:
GENERIC LOAD * INLINE [
item_ptr, groupname, labelname, labelvalue
];

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
mkudis
Contributor II
Contributor II
Author

It loaded that time but it doesn't seem to have changed the data model, as my initial load query gave me the same four column table.  I was hoping to get a table where all possible labelnames would have their own columns and labelvalue would be assigned appropriately in those columns.  In this case, instead of multiple rows for each item_ptr for every different labelname that is assigned to it, I would like an outcome of each distinct item_ptr having its own row.

gp_oconnor
Partner
Partner

'Temp Load':
load * inline
[Item Code, Item Description, Label Name, Label Value
ItemABC, ABCdescription, Color Family, Brown
ItemABC, ABCdescription, Product Type, Siding
ItemABC, ABCdescription, Style, Trim
ItemXYZ, XYZdescription, Color Family, White
ItemXYZ, XYZdescription, Product Type, Siding
ItemXYZ, XYZdescription, Style, Vertical Siding
ItemDEF, DEFdescription, Color Family, Black
ItemDEF, DEFdescription, Product Type, Trim
ItemDEF, DEFdescription, Style, Trim
]
;

'Data':
generic
load
[Item Code],
[Label Name],
[Label Value]
resident 'Temp Load'
;

for zTableNo = 0 to NoOfTables() - 1

let zTableName = TableName('$(zTableNo)');

if Left('$(zTableName)',5) = 'Data.' then

'Generic Tables':
load * inline
[Generic Table
$(zTableName)
]
;

end if;

next;


'Final Table':
load distinct
[Item Code],
[Item Description]
resident 'Temp Load'
;

drop table 'Temp Load';


for zIdx = 0 to NoOfRows('Generic Tables') -1

let zGenericTable = Peek('Generic Table',$(zIdx),'Generic Tables');

left join ('Final Table')
load
*
resident '$(zGenericTable)'
;

drop table '$(zGenericTable)';

next;

drop table 'Generic Tables';

mkudis
Contributor II
Contributor II
Author

I was able to produce my desired result by creating separate tables dependent on the Label Name with If statements.  It will take some maintenance if we add new label names at some point, but it is now working exactly as I have envisioned.  It created the pinwheel data model similar to what the generic load should, probably not the most desirable solution but it works. DataModel.JPG

View solution in original post