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

[SDT]

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

[alejandroquinones · Accepted solution]

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:

1. Steps 1 & 2: It loads your raw data and creates a master list (FinalTable) containing every unique PART number.

2. Step 3: It filters out the blanks and "NULL" agencies, assigning a 1 to everything that is left (your flag).

3. 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.

4. 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!