topic Re: Variable evaluation for excel sheet name in QlikView

Variable evaluation for excel sheet name

tamilarasu — Thu, 11 Jun 2015 05:34:04 GMT

I am taking sheet names by looping through excel workbooks. After that i need to replace the double single quotes with just a single quote. I have tried the below code but its not working.

My vSheetName value is Apr''20. After replace it should be Apr'20

Table:
Load *
From $(File)
(ooxml, embedded labels, table is $(vSheetName), filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))));

I have tried below:

Table:
Load *
From $(File)
(ooxml, embedded labels, table is $(replace(vSheetName,chr(39)&chr(39),chr(39))), filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))));

Could anyone correct me?

Re: Variable evaluation for excel sheet name

sushil353 — Thu, 11 Jun 2015 05:59:57 GMT

Hi,

Try $(chr(39)&Purgechar(vSheetName,'""')&chr(39) )

or $(=chr(39)&Purgechar(vSheetName,'""')&chr(39) )

HTH

Sushil

Re: Variable evaluation for excel sheet name

tamilarasu — Thu, 11 Jun 2015 06:06:42 GMT

Not working.

Re: Variable evaluation for excel sheet name

Siva_Sankar — Thu, 11 Jun 2015 06:12:07 GMT

Tamil,

Refer this thread https://community.qlik.com/thread/3915

Re: Variable evaluation for excel sheet name

tamilarasu — Thu, 11 Jun 2015 07:03:28 GMT

Siva,

I have tried but not working as expected.

Re: Variable evaluation for excel sheet name

Siva_Sankar — Thu, 11 Jun 2015 07:06:19 GMT

can you upload your file?

Re: Variable evaluation for excel sheet name

Siva_Sankar — Thu, 11 Jun 2015 07:24:49 GMT

Tamil,

You already got the sheet names in single quotes in yesterday's post. May i know how you got the double quotes?? find the attachment

Re: Variable evaluation for excel sheet name

sushil353 — Thu, 11 Jun 2015 07:44:29 GMT

Try this one: =SubField(vSheetName,'"',1)&chr(39)&SubField(vSheetName,'"',2)

Re: Variable evaluation for excel sheet name

tamilarasu — Thu, 11 Jun 2015 07:48:41 GMT

Hi Siva,

Yes. I thought to tell u. But When I adapt the code to my original data it again showing as double quotes. I don't know the exact reason as I am getting synthetic keys and one weird error.

Let me explain my requirement in simple words (Say for example).

I have two excel files and two sheets in each workbook. I want to loop through the sheets and should concatenate the sheets (No join) . Finally I need a single table like below attached file.

Could you check and give me the code (I have tried to concatenate but I am getting synthetic key). I am new to this field, so I am not able to figure it out.

I really appreciate your help. !