Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cwitman1
Contributor III
Contributor III

Using a Variable to determine Field Name

 

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;
Labels (5)
1 Solution

Accepted Solutions
4 Replies
anushree1
Specialist II
Specialist II

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/ScriptRegularS...

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

mark_costa
Partner
Partner

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

rwunderlich

mark_costa
Partner
Partner

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

Thank you,

Mark Costa