Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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?

18 Replies
sunny_talwar

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

Not applicable
Author

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);

Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

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