topic Re: Formatting Data from column to row Qlikview in QlikView

Formatting Data from column to row Qlikview

mkudis — Wed, 28 Jul 2021 20:14:13 GMT

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?

Re: Formatting Data from column to row Qlikview

Vegar — Thu, 29 Jul 2021 07:00:45 GMT

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!

Re: Formatting Data from column to row Qlikview

mkudis — Thu, 29 Jul 2021 12:25:31 GMT

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'
;

Re: Formatting Data from column to row Qlikview

Vegar — Thu, 29 Jul 2021 12:30:32 GMT

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
];

Re: Formatting Data from column to row Qlikview

mkudis — Thu, 29 Jul 2021 12:40:37 GMT

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.

Re: Formatting Data from column to row Qlikview

gp_oconnor — Thu, 29 Jul 2021 14:55:55 GMT

'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';

Re: Formatting Data from column to row Qlikview

mkudis — Thu, 29 Jul 2021 15:03:01 GMT

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.