1

I have a dataset of the following structure:

Company.ID  DDR (25632) PTL (89567)
2512             89         74
9875             78         96
7892             14         73

I would like to split the header into two different lines. With other words the second part of the header should or could be the first variable. How is possible to transform the dataset into the desired form (see below):

Company.ID          DDR         PTL 
     -            (25632)     (89567)
    2512             89         74
    9875             78         96
    7892             14         73

To replicate the above example in Qlik, run the code below:

LOAD * Inline [
        [Company.ID], [DDR (25632)], [PTL (89567)]
        2512,89,74
        9875,78,96
        7892,14,73
    ];

Any help or tipp would be highly appreciated!

And_R
  • 1,647
  • 3
  • 18
  • 32
  • Are you looking to transform the raw data specifically, or, is this a means to an end to get the header to display on multiple lines in a straight table or dynamic table? – bdiamante Apr 03 '19 at 14:47

1 Answers1

1

You need to loop columns, rename them and concatenate with new values. Here is example which I've written:

table:
LOAD * Inline [
        Company.ID, DDR (25632), PTL (89567)
        2512,89,74
        9875,78,96
        7892,14,73
    ];


For i=1 to NoOfFields('table')

    LET vField = FieldName($(i),'table');
    LET vFieldName_$(i) = SubField('$(vField)',' ',1);
    LET vFieldValue_$(i) = SubField('$(vField)',' ',2);

    If '$(vField)' <> '$(vFieldName_$(i))' THEN

        Rename Field '$(vField)' TO '$(vFieldName_$(i))'; 

    EndIf

next

Concatenate(table)
Load * Inline [
    '$(vFieldName_1)', '$(vFieldName_2)', '$(vFieldName_3)'
    '$(vFieldValue_1)', '$(vFieldValue_2)', '$(vFieldValue_3)'
];
Hubert Dudek
  • 1,666
  • 1
  • 13
  • 21
  • Thanks for your answer, Qlik Community here is quite small. Your code will surely work for a small table, but if you have a table of length 100, so it would be appropriate to concatenate each of the vFieldNames and vFieldValues in just one vFieldNameConsolidated and vFieldValuesConsolidated. I have already implemented this! Glad to meet you. – And_R Apr 05 '19 at 21:46
  • Hi thank you. Can you select my answer as correct or add answer by yourself to close this question as answered? Bests regards Hubert – Hubert Dudek May 08 '19 at 00:01