Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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?

Tags (2)
18 Replies
Highlighted

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

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

Highlighted
Not applicable

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

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)

Highlighted

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

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

Highlighted

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

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

Highlighted
Not applicable

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

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?

Highlighted

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

Did you look at the sample I provided below?

Highlighted
Not applicable

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

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

Highlighted
Not applicable

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

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

Highlighted

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

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