Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
robbybalboa
Contributor III
Contributor III

Script Load Content as Header

Dear all,

I have tried to find a solution for this problem.
Transforming content into a different format.

material_noATTRIBUTEVALUEATTRIBUTE
1saleschannelNonElectronics
1salesregionASIA
1assortmentBags
2saleschannelComputer
2salesregionEMEA
2assortmentKeyboard
3saleschannelNonElectronics
3salesregionall
3assortmentCleaning

Result

material_nosaleschannelsalesregionassortment
1NonElectronicsASIABags
2ComputerEMEAKeyboard
3NonElectronicsallCleaning

I had not success with the crosstable load or similar wizard options.

Thank you for any support and ideas
Robert

7 Replies
fvelascog72
Partner - Specialist
Partner - Specialist

Hi,

Try this:

Data:

LOAD

*

Inline [

material_no, ATTRIBUTEVALUE, ATTRIBUTE

1, saleschannel, NonElectronics

1, salesregion, ASIA

1, assortment, Bags

2, saleschannel, Computer

2, salesregion, EMEA

2, assortment, Keyboard

3, saleschannel, NonElectronics

3, salesregion, all

3, assortment, Cleaning

];

NoConcatenate

ATTRIBUTEVALUE_TABLE:

LOAD Distinct

  ATTRIBUTEVALUE

Resident Data;

LET sFields = NoOfRows('ATTRIBUTEVALUE_TABLE')-1;

FOR i = 0 to $(sFields);

  LET sFieldName = Peek('ATTRIBUTEVALUE', $(i), 'ATTRIBUTEVALUE_TABLE');

  TEMP1:

  LOAD

  material_no,

  ATTRIBUTE as $(sFieldName)

  Resident Data

  Where ATTRIBUTEVALUE = '$(sFieldName)';

NEXT i;

DROP Table Data;

DROP Table ATTRIBUTEVALUE_TABLE;

robbybalboa
Contributor III
Contributor III
Author

Hello Federico,
Thank you for your fast answer.

How can I create one table with this statement?

petter
Partner - Champion III
Partner - Champion III

The very simplest approach is as follows:

LOAD

  material_no,

  MaxString(saleschannel) AS saleschannel,

  MaxString(salesregion) AS salesregion,

  MaxString(assortment) AS assortment

GROUP BY

  material_no;

LOAD

  material_no,

  If( ATTRIBUTEVALUE = 'saleschannel' , ATTRIBUTE ) AS saleschannel,

  If( ATTRIBUTEVALUE = 'salesregion' , ATTRIBUTE ) AS salesregion,

  If( ATTRIBUTEVALUE = 'assortment' , ATTRIBUTE ) AS assortment

;

LOAD * INLINE [

material_no ATTRIBUTEVALUE ATTRIBUTE

1 saleschannel NonElectronics

1 salesregion ASIA

1 assortment Bags

2 saleschannel Computer

2 salesregion EMEA

2 assortment Keyboard

3 saleschannel NonElectronics

3 salesregion all

3 assortment Cleaning

] (delimiter is \t);

//

What you put from line 14-25 could be some other LOAD statement as long as it contains the necessary columns from a spreadsheet or a SQL database or some other source.

swuehl
MVP
MVP

I believe the very simplest approach is like

The Generic Load

Data:

GENERIC LOAD

*

Inline [

material_no, ATTRIBUTEVALUE, ATTRIBUTE

1, saleschannel, NonElectronics

1, salesregion, ASIA

1, assortment, Bags

2, saleschannel, Computer

2, salesregion, EMEA

2, assortment, Keyboard

3, saleschannel, NonElectronics

3, salesregion, all

3, assortment, Cleaning

];

In Henric's blog is also described how to combine the tables into one again, and why he believes you shouldn't do that.

petter
Partner - Champion III
Partner - Champion III

He asked for a single table ... so ...

Furthermore it depends what the real data is - make note of what HIC says in his article:

     The result is one big table that contains all attributes; a table that often is sparse

This does not look like a typical sparse table case - unless the sample data is very simplified. I definitely think

Generic Load is excellent with many attributes and sparse data.

swuehl
MVP
MVP

Sure, I don't mind to re-combine the tables back into a single one. The recipe is there.

A big advantage of a generic load over your approach is that you don't need to hardcode the values you expect in ATTRIBUTEVALUE. It's dynamic.

petter
Partner - Champion III
Partner - Champion III

That is true. Just to be clear what I meant is that by saying "simplest" I really meant simplistic - and not flexible as the Generic Load certainly is. I still think that Generic Load is not the ultimate solution that should be used in every case where you need an unpivot. One disadvantage is that you get multiple tables that might clutter the clarity of your data model for business users.