multiple column fill

[Anonymous]

using the wizard in script to replace 'NA' with the value below when iporting table file  - but it let me do it only column by column...I have 100 column ...

....

(ooxml, embedded labels, table is Grade, filters(

Replace(3, bottom, StrCnd(equal, 'NA')),

Replace(4, bottom, StrCnd(equal, 'NA'))

));

Maybe a loop or something else clever?

any idea?

Thanks

Raph

[vishsaggi]

Can you show some sample data and what you want to replace 'NA' with?

[YoussefBelloum]

Hi,

2 questions:

1.     you have 100 columns on which you want to remove NA values ?

2.     you really have NA in your source ? or it is blanks/NULLS replaced in the front end by NA ?

[Anonymous]

1, yes cross table

2.really NA, but at source I can change it to blank

[marcus_sommer · Accepted solution]

Just transform your data with The Crosstable Load and then you need to adjust/replace the missing values respectively NULL in only one column (and a "normal" table-structure will be quite easier to handle in many other things too).

- Marcus

[Anonymous]

Many thanks

[YoussefBelloum]

Hi marcus_sommer‌

if we have a normal table with hundreds of columns (Measures) ?

[marcus_sommer]

I think quite probably yes. Crosstables needs some resources during the transforming and therefore it might be useful to split the logic but I would do it only if I run into serious performances issues by loading the whole table.

- Marcus

[YoussefBelloum]

Ok. But If we have a restriction changing the table structure? because pivoting Measures columns will change all the front end expressions logic.. is there a way to pivot on the script (using crosstable load), make this type of transformations and do like a "reverse crosstable" ?

[marcus_sommer]

Yes, there is with The Generic Load but I'm not sure if it is really sensible to transform the crosstable, doing a few transformations and reverse it again. Regarded to the origin question here I would check if I could solve the task with the various Qlik NULL variables like NullAsValue and so on or with a mapping approach like this one: Filling Default Values Using Mapping | Qlikview Cookbook‌.

Beside this I would really consider to change the whole datamodel and all of the gui-expressions at least by the next business-release of the application. There are not very many "good" reasons to use a crosstable-datastructure within the calculations and by hundreds of columns I doubt it and think it's rather a nightmare to work with ...

- Marcus