topic Re: multiple column fill in QlikView

multiple column fill

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: multiple column fill

vishsaggi — Wed, 11 Apr 2018 14:42:26 GMT

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

Re: multiple column fill

YoussefBelloum — Wed, 11 Apr 2018 14:43:05 GMT

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 ?

Re: multiple column fill

Anonymous — Wed, 11 Apr 2018 14:44:31 GMT

1, yes cross table

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

Re: multiple column fill

marcus_sommer — Wed, 11 Apr 2018 14:50:08 GMT

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

Re: multiple column fill

Anonymous — Wed, 11 Apr 2018 14:51:56 GMT

Many thanks

Re: multiple column fill

YoussefBelloum — Wed, 11 Apr 2018 15:11:49 GMT

Hi marcus_sommer‌

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

Re: multiple column fill

marcus_sommer — Wed, 11 Apr 2018 15:22:17 GMT

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

Re: multiple column fill

YoussefBelloum — Wed, 11 Apr 2018 15:28:53 GMT

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" ?

Re: multiple column fill

marcus_sommer — Wed, 11 Apr 2018 15:47:32 GMT

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

Re: multiple column fill

YoussefBelloum — Wed, 11 Apr 2018 15:56:57 GMT

Thank you very much for the advices Sir