How can I unpivot data and add year field pulling from a row?

[Qlik_dev03]

I am dealing with data from an excel file that looks like Chart A but I want to load it to look like chart B (photos attached) Fields and Data is just an example and not the actual data I'm analyzing. I could get it to load but my big issue is trying to pull year from that row. Thanks in advance for the help. 

[JonnyPoole]

Can you share a sample that shows multiple years? I can give you a sample, but unless I see how it looks with multiple years, it wouldn't work when you go to try it with multiple years

[Qlik_dev03]

So this is the format of a report I will receive monthly. IT will get dropped into a folder to be loaded by qlik. Each file will have the year it applies to just as you see in the example I gave. In other words everything from this file is from 2021 and there are other files with their years located in the same location.

Hope that makes sense (pun intended) 😂

[Anil_Babu_Samineni]

@Qlik_dev03 You can try like this?

CrossTable(Metric, Score)
LOAD Location,
F1,
F2,
F3,
F4
FROM
[YourSource]
(ooxml, embedded labels, table is Sheet1) Where Not IsNull(Location);

CrossTable(Year, Score)
LOAD F1,
[2021],
[20211] as [2021],
[20212] as [2021],
[20213] as [2021]
FROM
[YourSource]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

[Qlik_dev03]

@Anil_Babu_Samineni Very Close. When I use this script I loose Metric. I need Metric, Year and Score in the same table. 

[JonnyPoole]

This worked for me. And it handles a changing number of rows, columns (metrics), and years 

ExcelContents:

LOAD 

*

FROM [lib://Community Answers:DataFiles/2428811.xlsx]

(ooxml, embedded labels, table is Sheet1);

let vFirstMetricField=FIELDNAME('2','ExcelContents');

LET vYear=PEEK('$(vFirstMetricField)',0,'ExcelContents');

LET vNumFields=NOOFFIELDS('ExcelContents');

FOR field = 2 TO $(vNumFields)

LET vField=FIELDNAME('$(field)','ExcelContents');

Data:

LOAD

    Location,

        '$(vField)' AS Metric,

        [$(vField)] AS Score,

        $(vYear) AS Year

RESIDENT ExcelContents

    WHERE [$(vField)]<>$(vYear);

NEXT field

DROP TABLE ExcelContents

[PhanThanhSon]

Hi,

it was indeed a bit challenging to dynamically structure this task, especially with an unknown number of columns. You can find the QVF file as well as the Excel file attached. That should meet your requirements.

Let vPath = 'lib://Git-Test/script/';

Pivot:
First 2 LOAD
   *
FROM [$(vPath)Pivot.xlsx]
(ooxml, no labels, table is Tabelle1); // no labels is important

Let vConcatenateName = '';
FOR i = 1 to NoOfFields('Pivot')
  Let vFieldName = FieldName(i ,'Pivot');
  Let vConcatenateName$(i) = FieldValue('$(vFieldName)', 1) & '-' & FieldValue('$(vFieldName)', 2);
  Let vConcatenateName = '$(vConcatenateName)' & FieldValue('$(vFieldName)', 1) & '-' & FieldValue('$(vFieldName)', 2) & ',';
NEXT i

LET i =;
DROP TABLE Pivot;

Pivot:
Load * Inline [
$(vConcatenateName)
];

PivotTemp:
LOAD
    *
FROM [$(vPath)Pivot.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Tabelle1); //header is 1 lines

Let vFieldNameFinal = '';
FOR i = 1 to NoOfFields('PivotTemp')
  Let vFieldName = FieldName(i ,'PivotTemp');
  Let vFieldNameFinal = '$(vFieldNameFinal)' &  '[' & '$(vFieldName)' &  ']'& ' AS '  & '[' &'$(vConcatenateName$(i))' & ']' & ',' ;
  Let vConcatenateName$(i) =;
NEXT i
LET i =;

DROP TABLE PivotTemp;
Let vFieldNameFinal = LEFT('$(vFieldNameFinal)',len('$(vFieldNameFinal)')-1); //Eliminate last comma
Concatenate(Pivot)
LOAD
    $(vFieldNameFinal)
FROM [$(vPath)Pivot.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Tabelle1);

FinalTableTemp:
Crosstable (MetricTemp, Score)
Load *
Resident Pivot;

Let vFieldNameFinal =;
Let vPath =;
Let vConcatenateName =;
Let vFieldName=;


NoConcatenate
FinalTable:
LOAD [Location-] AS Location,
    Subfield(MetricTemp, '-', 1) AS Metric,
    Subfield(MetricTemp, '-', 2) AS Year,
    Score
Resident FinalTableTemp;

Drop table FinalTableTemp, Pivot;
Let vFieldNameFinal =;
Let vPath =;
Let vConcatenateName =;
Let vFieldName=;

Best regards Son