topic Re: Script challenge ;) in QlikView

Script challenge ;)

blunckc1 — Wed, 27 Jun 2018 06:54:56 GMT

Ok folks, I am being 100% lazy, can some write the script needed to achieve the following?

I have data in 5 separate columns that need to be combined into one, the kicker being that my users don't follow the logic in how that data SHOULD be entered resulting in some columns containing nulls with others containing values.

Here are the 5 columns, with the needed transformation because of how the data comes in from SharePoint:

	SubField(ows_Other_x0020_Contributor_x0020_1,'#',-1)						as [Other Contributor 1],
	SubField(ows_Other_x0020_Contributor_x0020_2,'#',-1)						as [Other Contributor 2],
	SubField(ows_Other_x0020_Contributor_x0020_3,'#',-1)						as [Other Contributor 3],
	SubField(ows_Other_x0020_Contributor_x0020_4,'#',-1)						as [Other Contributor 4],
	SubField(ows_Other_x0020_Contributor_x0020_5,'#',-1)						as [Other Contributor 5],

Result is a new column being created called [Other contributors combined].

If there are values in columns other contributor 1 (bob), 3 (terry) and 4 (sam), the result would be bob; terry; sam.

If there are values in column other contributor 4 (jack) and 5 (phil), the result would be jack; phil.

If there is only a values in column other contributor 1 (jack), the result would be jack.

If there are no values in any of the columns, the result would be . That is to represent a null return.

Thanks in advance!

Re: Script challenge ;)

olivierrobin — Wed, 27 Jun 2018 07:37:52 GMT

hello

maybe something like that (you should adapt it to your requirements)

load * inline [

a,b,c,d

a,b,c

a,b

,b,c

,b,c,d

,,c,d

]

;

set s=if(len(trim($1))>0,$1 & ',');

t2:

load

left($(s(a)) & $(s(b)) &$(s(c)) & $(s(d)),len($(s(a)) & $(s(b)) &$(s(c)) & $(s(d)))-1) as z

resident t;

exit script;

Re: Script challenge ;)

tamilarasu — Wed, 27 Jun 2018 08:17:38 GMT

Hi Carl,

You can also try like below

Map:
Mapping Load 
Repeat(' ', RecNo()), ';'
AutoGenerate 4;
 
Data:
Load *, MapSubString('MAP', Trim(A & ' ' & B & ' ' & C & ' ' & D & ' ' & E)) as NewField;
LOAD * INLINE [
    A,B,C,D,E
    1,2,3,4,5
    1,,2,3,4
    ,,,,1
    ,,2
    ,,,5
    1
    3,,,,4
];

Re: Script challenge ;)

blunckc1 — Thu, 28 Jun 2018 01:31:54 GMT

Thanks mate - this worked as expected!

Re: Script challenge ;)

blunckc1 — Thu, 28 Jun 2018 01:34:18 GMT

Thanks Tamil - tried this but was getting this error

Error in expression:

MapSubString function takes a constant map as first parameter.

Olivier's solution worked so I'm all good.

Re: Script challenge ;)

tamilarasu — Thu, 28 Jun 2018 08:32:42 GMT

Hi Carl,

It was my fault. I just typed the table name in caps by mistake. Thought of sharing the correct solution. Have a fantastic day!

MapSubString('MAP', Trim(A & ' ' & B & ' ' & C & ' ' & D & ' ' & E))

MapSubString('Map', Trim(A & ' ' & B & ' ' & C & ' ' & D & ' ' & E))