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: 
lmc
Contributor
Contributor

Consolidate multiple columns and average value

I am trying to combine items 1 thru 9 in to a single column while averaging the value listed in diff 1 thru 9.
some of the items are repeated in multiple columns as well as row.
below is a small snip of what I am working with. and in column AE and AF is what I am trying to get.

Capture.PNG

1 Solution

Accepted Solutions
Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi there

I made a few assumptions, but the following should work (just replace the part in red to point to your data):

OriginalTable:
LOAD
*
FROM [lib://Temp/Consolidate multiple columns.xlsx]
(ooxml, embedded labels, table is Sheet1);


Let vNumberOfFields = NoOfFields('OriginalTable');
Let vMaxNum = 0;

For x = 1 to vNumberOfFields

Let ColumnNumber = Right(FieldName($(x),'OriginalTable'),1);
If Left(FieldName($(x),'OriginalTable'),4) = 'item' and IsNum($(ColumnNumber)) Then
If ColumnNumber > vMaxNum Then
Let vMaxNum = $(ColumnNumber);
End If
End If

Next x

For i = 1 to vMaxNum

FinalTable:
Load
[item $(i)] AS Item,
[diff $(i)] AS Diff
Resident OriginalTable
Where Not IsNull([item $(i)]);

Next i;

Let vNumberOfFields = Null();
Let vMaxNum = Null();
Let ColumnNumber = Null();
Let i = Null();
Let x = Null();

DROP TABLE OriginalTable;

Then you can simply use a table in the front end with Item as a dimension and Avg(Diff) as the measure.

clipboard_image_0.png

Hope this helps.

Regards,

Mauritz

View solution in original post

1 Reply
Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi there

I made a few assumptions, but the following should work (just replace the part in red to point to your data):

OriginalTable:
LOAD
*
FROM [lib://Temp/Consolidate multiple columns.xlsx]
(ooxml, embedded labels, table is Sheet1);


Let vNumberOfFields = NoOfFields('OriginalTable');
Let vMaxNum = 0;

For x = 1 to vNumberOfFields

Let ColumnNumber = Right(FieldName($(x),'OriginalTable'),1);
If Left(FieldName($(x),'OriginalTable'),4) = 'item' and IsNum($(ColumnNumber)) Then
If ColumnNumber > vMaxNum Then
Let vMaxNum = $(ColumnNumber);
End If
End If

Next x

For i = 1 to vMaxNum

FinalTable:
Load
[item $(i)] AS Item,
[diff $(i)] AS Diff
Resident OriginalTable
Where Not IsNull([item $(i)]);

Next i;

Let vNumberOfFields = Null();
Let vMaxNum = Null();
Let ColumnNumber = Null();
Let i = Null();
Let x = Null();

DROP TABLE OriginalTable;

Then you can simply use a table in the front end with Item as a dimension and Avg(Diff) as the measure.

clipboard_image_0.png

Hope this helps.

Regards,

Mauritz