Read from excel replace the value then store it back in same excel

qlikviewforum — Wed, 17 Jul 2013 17:26:49 GMT

Hi,

I have couple of excel in a folder and in all the excels I have field called PATH which contain some location.Based on the value in one of the field of another other table I want to replace the value of PATH field in the excel sheet and save it with the same name.For example if the value of the field is LEAVE then I will be replace the value as shown below if not location will remain same it will not be replaced.

(i,e If the value in a path is C:/Desktop/Folder1/Folder2 i want to replace it to D:/Desktop/Folder1/Folder3 if the value of the PATH is LEAVE, if not PATH will remain same C:/Desktop/Folder1/Folder2).I am aware that I can use REPLACE function to replace the same.

replace('Path','C:/Desktop/Folder1/','D:/Desktop/Folder1') returns 'D:/Desktop/Folder1/Folder3'

Also I would say that all the excel is in the format "ExcelName_YYYYDD".So after reading and replace the value in excel I need to store it back with same name only. For example if I have below files I need to save it with the same name only.

ExcelName_201301.xls

ExcelName_201302.xls

ExcelName_201303.xls

ExcelName_201304.xls

ExcelName_201305.xls

Hope my requirement is understand if not please let me know. Hope some one can help me on this soon.

I want to achieve above requirement without the use of Macro.

Regards,

qvforum

topic Read from excel replace the value then store it back in same excel in QlikView

Read from excel replace the value then store it back in same excel