FieldValueList() Usage Scenarios

[Miles_Dyson]

Hello experts,

What are some examples of real life use cases of the FieldValueList() function in For Each..Next loops. And how does the usage scenarios differ from utilizing the LOAD DISTINCT function?

Thank you

[marksouzacosta · Accepted solution]

Hi @Miles_Dyson ,

This is a very broad question but let me try to give you some guidance.

First, there is no straight relationship between FieldValueList and LOAD DISTINCT. They are built for different purposes.

Load Distinct is very straight forward. It is used to load unique rows, not matter the number of columns you have in your Data Source.

FieldValueList lists unique values of a column. So, if you have a column State, with multiple repeated values, FieldValuesList will only lists distinct values - maybe this is why you are relating Load Distinct with FieldValueList. It is mostly used in loops.

I use very often FieldValueList in multiple different scenarios.

One simple example, let's say you have an Excel Spreadsheet with multiple Sales sheets with the same structure, but every sheet is a different Year, so the Sheet name is the Year. You can load your data with the code below:

TempSheetName:
LOAD * INLINE [
SheetName
2020
2022
2023
2024
];

For Each vSheetName in FieldValueList('SheetName')

	TRACE Loading Sheet $(vSheetName);
    
    [Sales]:
    LOAD
        FieldA,
        FieldB,
        FieldC,
        FieldD
    FROM [lib://DataFiles/MySpreadsheet.xlsx]
    (ooxml, embedded labels, table is [$(vSheetName)])
    ;

Next vSheetName;

DROP TABLE TempSheetName;

There are many many other scenarios where you can use FieldValueList. It is one of my favorite functions in Qlik.

I hope I have helped.

Regards,

Mark Costa

[Miles_Dyson]

Yes you have...the exercise example put things into perspective. I get it now. Thank you.