Convert Field Contents into Separate Fields (Flags Y/N)

SDT — Wed, 10 Jun 2026 18:42:49 GMT

I have data with two columns (example below). One is a part number and the other is an agency name.

What I need is to create a table with a field for part number and separate fields to be flagged "Y/N" or "1/0" if that part aligns with that Agency. So if there were 10 Agencies in total, I would have 11 fields in the new table.

The number and content of the Agency field is dynamic. The only time Agency will be NULL is if that part is not associated with any agencies.

I'm hitting a wall with how to accomplish this. Any help would be greatly appreciated.

PART AGENCY

123 Company1

123 Company4

345 NULL

456 Company1

456 Company3

Re: Convert Field Contents into Separate Fields (Flags Y/N)

alejandroquinones — Wed, 10 Jun 2026 20:38:30 GMT

Hey @SDT ,

Is this what you are expecting?

If so, you need to use the GENERIC LOAD prefix to turn every distinct value in the AGENCY field into a new column. Find attached the .qvf file. Here is the code, and basically how it works:

Steps 1 & 2: It loads your raw data and creates a master list (FinalTable) containing every unique PART number.
Step 3: It filters out the blanks and "NULL" agencies, assigning a 1 to everything that is left (your flag).
Step 4. The Generic load is a special Qlik function that takes your vertical list and shatters it into a bunch of temporary mini-tables, one dedicated to each unique AGENCY.
Step 5: The for loop joins the table created in steps 1 & 2 for each agency.

// 1. Load the raw data using INLINE RawData: LOAD * INLINE [ PART, AGENCY 123, Company1 123, Company4 345, NULL 456, Company1 456, Company3 ]; // 2. Create the base table with ALL distinct PART numbers FinalTable: LOAD DISTINCT PART RESIDENT RawData; // 3. Prepare data for the Generic Load Generic_Prep: LOAD PART, AGENCY, 1 as FlagValue RESIDENT RawData WHERE Len(Trim(AGENCY)) > 0 AND AGENCY <> 'NULL'; // 4. Perform the Generic Load AgencyFlags_Tmp: GENERIC LOAD PART, AGENCY, FlagValue RESIDENT Generic_Prep; DROP TABLE Generic_Prep; //5. Left join the table with all PART values for every agency FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); //Just join tables with the following pattern IF WildMatch('$(vTable)', 'AgencyFlags_Tmp.*') THEN LEFT JOIN (FinalTable) LOAD * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i DROP TABLE RawData;

If you also need to turn the null values into 0, I tried using the NullAsValue statement, but it's not working for me—apparently because of the LEFT JOIN. Maybe someone can help us out here; I haven't used this statement much.

However, I managed to do it another way with another FOR loop. In the .qvf file, you'll see two tabs: 'Generic Load (v1)' is the version with the flags as 1 and null values, and the 'Generic Load (v2)' tab contains an extra step to clean these values to 0.

Help on Generic load: https://qlikviewcookbook.com/2010/05/use-cases-for-generic-load/

Hope this helps!

topic Convert Field Contents into Separate Fields (Flags Y/N) in App Development

Convert Field Contents into Separate Fields (Flags Y/N)

Re: Convert Field Contents into Separate Fields (Flags Y/N)