Variable evaluation for excel sheet name

[tamilarasu]

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?

[sushil353]

Hi,

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

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

HTH

Sushil

[tamilarasu]

Not working.  

[Siva_Sankar]

Tamil,

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

[tamilarasu]

Siva,

I have tried but not working as expected.

[Siva_Sankar]

can you upload your file?

[Siva_Sankar]

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

[sushil353]

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

[tamilarasu]

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