macro to replace character in field table

[rafael5958]

Im creating a table in macro and and I have to replace all the "," for ";" in all column and rows using macro. How can I do that?

[jaibau1993]

Hi!

What kind of table are you creating (straight table, pivot table, table object, table in data model)?

You may use the vbs function Replace if you can run over each cell of the table.

Regards!

Jaime.

[rafael5958]

Its a straight table, I have to replace all values in all cells of the table.

aaa;aaa 

bbb ; bbb

aaa;;aaaa

a;a;a;a;a;;

should looks like:

aaa,aaa

bbb , bbb

aaa;;aaa

a,a,a,a,a,,

[jaibau1993]

Hi!

If it is a straight table then there will be dimension and expression columns. 

• Dimensions: In order to replace ',' with ';' you have to use a calculated dimension or you have to modify the script to load your dimension with the apropiate ',' replaced. In both cases, use Replace QlikView function. Example of calculated dimension: use Replace(DimensioA, ',', ';') instead of DimensionA
• Expressions: If you have expressions too, then add an outer Replace function over your expression. Example: if your expression is Only(Product) then use Replace(Only(Product), ',', ';')

Regards,

Jaime.

[rafael5958]

Im trying but not working, could you show me with the example added??

[jaibau1993 · Accepted solution]

Hi!

Please, check the attached file. I re-wrote your code because you were using a Table Box object, not a Straight Table object (the table box does not allow calculated dimensions). Plus, I added some code to rename dimension labels and allow zero values.

Let me know your doubts!

Regards,

Jaime.

[rafael5958]

Thank you very much. It worked perfectly.

Is it possible to use it Table Object?  I will have to do it eventually.

Tried with tb.AddField "=Replace(columnA,';',',')" 

but didnt work.

[jaibau1993]

Nope, you can't (as far as I know). A table object only display data as it is, you can not build a calculated dimension nor expression (you have to use a straight or pivot table for that).

There is a workaround, though. If you add in your load script something like Replace(columnA,';',',') as columnA_Modified then you can use the field colmnA_Modified in your table object.

Regards,

Jaime.

[rafael5958]

Thank you.