topic Re: macro to replace character in field table in QlikView

macro to replace character in field table

rafael5958 — Sat, 16 Nov 2024 02:11:45 GMT

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?

Re: macro to replace character in field table

jaibau1993 — Fri, 18 Oct 2019 07:34:12 GMT

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.

Re: macro to replace character in field table

rafael5958 — Fri, 18 Oct 2019 12:49:39 GMT

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

Re: macro to replace character in field table

jaibau1993 — Fri, 18 Oct 2019 13:40:19 GMT

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.

Re: macro to replace character in field table

rafael5958 — Sat, 19 Oct 2019 14:55:42 GMT

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

Re: macro to replace character in field table

jaibau1993 — Mon, 21 Oct 2019 06:53:49 GMT

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.

Re: macro to replace character in field table

rafael5958 — Mon, 21 Oct 2019 12:35:37 GMT

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.

Re: macro to replace character in field table

jaibau1993 — Mon, 21 Oct 2019 13:27:15 GMT

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.

Re: macro to replace character in field table

rafael5958 — Mon, 21 Oct 2019 13:32:11 GMT

Thank you.