# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New 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. 1 Solution

Accepted Solutions
Contributor III

## Re: Consolidate multiple columns and average value

Hi there

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

OriginalTable:
*
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:
[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. Hope this helps.

Regards,

Mauritz

Contributor III

## Re: Consolidate multiple columns and average value

Hi there

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

OriginalTable:
*
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:
[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. Hope this helps.

Regards,

Mauritz