topic Re: Select column based on an if statement - does not work? (excel source) in Connectivity & Data Prep

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

Wed, 16 Mar 2016 16:14:26 GMT

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?

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

sunny_talwar — Wed, 16 Mar 2016 16:25:18 GMT

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

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

Thu, 17 Mar 2016 10:55:58 GMT

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)

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

sunny_talwar — Thu, 17 Mar 2016 11:06:45 GMT

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

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

sunny_talwar — Thu, 17 Mar 2016 12:08:52 GMT

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

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

Thu, 17 Mar 2016 15:40:48 GMT

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?

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

sunny_talwar — Thu, 17 Mar 2016 15:42:19 GMT

Did you look at the sample I provided below?

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

Thu, 17 Mar 2016 15:42:47 GMT

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

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

Tue, 22 Mar 2016 10:01:12 GMT

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

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

sunny_talwar — Tue, 22 Mar 2016 11:07:09 GMT

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

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

sunny_talwar — Tue, 22 Mar 2016 14:27:42 GMT

Tried it with Qlik Sense, seems to be working well Except for the fact that it doesn't really rename the column name in the front end for some reason. Try it out

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

Tue, 22 Mar 2016 15:33:35 GMT

Try add a file that is not matching "Change" and you will see it fail.- it will still look for the INC field in the CHG excel file - also....

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

sunny_talwar — Tue, 22 Mar 2016 15:36:18 GMT

I did and it works for me without any errors. I will give it a try one more time

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

sunny_talwar — Tue, 22 Mar 2016 15:39:18 GMT

Works as I would expect. Try one each time the attached two files. Do not have both of them sitting in the same folder when loading

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

Tue, 22 Mar 2016 16:01:06 GMT

Now I understand why we dont understand each other 🙂

the problem is just that i want to have these in the same folder at the same time and use the name as a variable to know what columns to load from each file. (in Sense Cloud, there is no folder structure possible)....

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

sunny_talwar — Tue, 22 Mar 2016 16:02:52 GMT

Atleast now we do. And are you trying to load both of them or just one of them? and if you will only load one of them, then how would you decide which one to load?

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

Wed, 23 Mar 2016 10:08:28 GMT

I want to load all files matching my path ie. \somefiles*.xls

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

sunny_talwar — Wed, 23 Mar 2016 10:23:04 GMT

I think that is much simpler that what we have been doing thus far. Here are some references while I play around with our current sample

Loading data from multiple xls-files from folder with subfolder

QlikView Maven: Loading All of the Files from a Folder

Dyanamic load of excel files from folder

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

Wed, 20 Apr 2016 14:07:19 GMT

Iam Sorry Sunny T, the examples is just addressing import of multiple files, that I got covered already

The thing is my files differs in format - ie. column names differs from file A to file B - but data there should go to same Sense column, I also want to to different things based on if a column is existing in a sheet or or not.

It does not seem to be a a way to check if a column is there or not - that is the exact functionality Iam after.

so far I worked-around this by some macros excelside to clean and format the data, it however adds

a manual step I would like to get rid off.