- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
macro to replace character in field table
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?
- Tags:
- qlikview_scripting
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Im trying but not working, could you show me with the example added??
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you.