Select column based on an if statement - does not work? (excel source)

[#1]

Hi, I got this statement - that checks part of the filename to decide what column name to use importing from sheet

and save that value into a field with a common name.

However it seems the importer evaluates the existence of BOTH Columns regardless if only one of them is to be used according

to the IF-statement (that does not check column values but the filename to avoid checking for Columns that does not exist)

If(Trim(Left(SubField(FileName(),'Open',2),8)) = 'Changes',[CHG],[INC]) As State,

... the problem is only either one or the other column name exists in each source-file, and import fails on the missing column

even if Iam not actually trying to import it imo.

I thought i could workaround by checking filename and decide what column to import based on that - but it seems I cannot?

[sunny_talwar]

Not sure I completely understand the issue, do you have an application you can share?

[#3]

what iam trying to do is based on the file name (in a batch of excel files LOAD)

import "column A" OR "column B" into "column C" - depending on the name of the file (using an if statement)

[sunny_talwar]

May be create a variable:

LET vField = If(Trim(Left(SubField(FileName(),'Open',2),8)) = 'Changes', '[CHG]', '[INC]')

and then in your LOAD, do this:

LOAD Fields,

          $(vField) as State

FROM Source....

[sunny_talwar]

Here is a sample script for you to look at (also find attached the data to play around with it)

Temp:
First 1
LOAD FileName() as FileName
FROM
[Field_Open*.xlsx]
(ooxml, embedded labels, table is Sheet1);

LET vField = If(Trim(Left(SubField(Peek('FileName'),'Open',2),7)) = 'Changes','[CHG]','[INC]');

DROP Table Temp;

Table:
LOAD Dim, 
    $(vField) as State
FROM
Field_Open*.xlsx
(ooxml, embedded labels, table is Sheet1);

[#6]

same issue occurs when trying to put the value in a variable

- the code seem to evaluate the existence of both columns [CHG] and [INC] regardless of the result from the evaluation on the if statement.

- since only one column exists at one time the import fails on "missing field",

even if iam not interested in importing the field that is not existing.

anyone have an idea on how to work around this?

[sunny_talwar]

Did you look at the sample I provided below?

[#8]

Let me look into your example, I was writing at the same time you replied with the example

[#9]

Sorry can't make that work in Qlik Sense - FileName ends up as NULL

[sunny_talwar]

Let me give it a try in QlikSense and get back to you