topic Re: Using a Variable to determine Field Name in App Development

Using a Variable to determine Field Name

cwitman1 — Sat, 16 Nov 2024 06:34:42 GMT

Hello,

I am wondering if it is possible to use a variable to choose what field name is necessary. The problem is a vendor sends csv files and they have changed their field name from User_Name to Employee_Name. I set up a temporary table to read which field name is used for a certain file. I then store the correct field name into a variable and load the actual table. Problem is the variable is storing 'User_Name' or 'Employee_Name' as the value of the field. Is it possible to set the field name as the variable but load in the data from the file.

Here is my script:

For Each vFile in FileList('$(vUserTable_Source)')

Tmp:
First 1 Load
        
          if(SubStringCount(
           '|' & @1&'|' &  '|' & @2& '|' & @3& '|' & @4& '|' & @5& '|' & @6& '|' & @7& '|' & @8& '|' & @9& '|' & @10&
           '|' & @11& '|' & @12& '|' & @13& '|' & @14& '|' & @15& '|' & @16& '|' & @17& '|' & @18& '|' & @19& '|' & @20&
           '|' & @21& '|' & @22& '|' & @23& '|' & @24& '|' & @25& '|' & @26& '|' & @27& '|' & @28& '|' & @29& '|' & @30&
           '|' & @31& '|' & @32& '|' & @33& '|' & @34& '|' & @35& '|' & @36& '|' & @37& '|' & @38& '|' & @39& '|' & @40&
           '|' & @41& '|' & @42& '|' & @43& '|' & @44& '|' & @45& '|' & @46& '|' & @47& '|' & @48& '|' & @49& '|' & @50& '|'
          ,'|User_Name |')>0,'User_Name','Employee_Name') as User_Name
          
from [$(vFile)]
(txt, codepage is 28591, no labels, delimiter is ',', msq);


LET vUser_Name = PEEK('User_Name', 0, 'Tmp');

drop table Tmp;


  UserTable:

      LOAD 
          FileTime() as File_Time,
          Email,
          "Recipient Name",
          "New/Repeat Reporter",
          "Reported Phish Timestamp",
          "Time to Report (in seconds)",
          "Remote IP",
          "GeoIP Country",
          "GeoIP City",
          $(vUser_Name) as User_Name,
          BUSINESS_GROUP,
          "USERID",
          EMPLID,
          DEPTID

      FROM [$(vFile)]
      (txt, codepage is 28591, embedded labels, delimiter is ',', msq);


next vFile;

Re: Using a Variable to determine Field Name

anushree1 — Fri, 15 Feb 2019 13:39:44 GMT

You could use REname here please check its functionality in the below link

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptRegularStatements/rename-field.htm

So here you can have the Excel with Changed Field names and deal with it accordingly

Re: Using a Variable to determine Field Name

marksouzacosta — Fri, 15 Feb 2019 15:26:54 GMT

Hi,

Maybe you can use this approach:

// Load the two data structures
[UserTable]:
LOAD * INLINE [
File_Time, Email, "Recipient Name", "New/Repeat Reporter","Reported Phish Timestamp","Time to Report (in seconds)","Remote IP","GeoIP Country","GeoIP City",User_Name,BUSINESS_GROUP,"USERID",EMPLID,DEPTID
];


[TempEmployeeTable]:
LOAD * INLINE [
File_Time, Email, "Recipient Name", "New/Repeat Reporter","Reported Phish Timestamp","Time to Report (in seconds)","Remote IP","GeoIP Country","GeoIP City",Employee_Name,BUSINESS_GROUP,"USERID",EMPLID,DEPTID
];

// Load all your data files without specifying any field or table name
// The data from the files will land automatically in one of the data structures loaded above
For Each vFile In FileList('lib://TestData/*.csv')
	
  LOAD
  	FileTime() AS File_Time,
	*
  FROM
      [$(vFile)]
      (txt, codepage is 28591, embedded labels, delimiter is ',', msq)
  ;

Next vFile


// Now rename the TempEmployeeTable in the way that you need and drop the TempEmployeeTable
// Note you can't use RENAME FIELD [Employee_Name] TO [User_Name] in this case here because User_Name already exists
CONCATENATE([UserTable])
LOAD
    File_Time, 
    Email, 
    "Recipient Name", 
    "New/Repeat Reporter",
    "Reported Phish Timestamp",
    "Time to Report (in seconds)",
    "Remote IP",
    "GeoIP Country",
    "GeoIP City",
    Employee_Name AS User_Name,
    BUSINESS_GROUP,
    "USERID",
    EMPLID,
    DEPTID
RESIDENT
	[TempEmployeeTable]
;

DROP TABLE [TempEmployeeTable];

Regards,

Mark Costa

Re: Using a Variable to determine Field Name

rwunderlich — Fri, 15 Feb 2019 16:58:20 GMT

It may be easiest to solve this by using ALIAS. See https://qlikviewcookbook.com/2018/12/loading-varying-column-names/

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Using a Variable to determine Field Name

marksouzacosta — Fri, 15 Feb 2019 18:42:05 GMT

@rwunderlich that is awesome, I didn't know about the ALIAS statement.

Thank you,

Mark Costa