topic Index() and SubStringCount() functions in QlikView

Index() and SubStringCount() functions

bmensing — Fri, 28 Jul 2017 14:06:46 GMT

Hello there,

lets say i have one table with three fields #, abc and def

# abc def

1 BIKE|CAR|TRUCK GREY|BLUE|RED

2 TRUCK|BIKE|CAR VIOLET|ORANGE|YELLOW

3 CAR|TRUCK|BIKE BLACK|WHITE|GREEN

and the position of the word bike in the second field is important to get the value in the third field e.g. for the

i need to know the values for BIKE for each #

1 = BIKE = GREY

2 = BIKE = ORANGE

3 = BIKE = GREEN

pls help me 🙂

Re: Index() and SubStringCount() functions

sunny_talwar — Fri, 28 Jul 2017 14:12:48 GMT

Try this

Table:

LOAD #,

abc,

SubField(def, '|') as def;

LOAD #,

SubField(abc, '|') as abc,

def;

LOAD * INLINE [

#, abc, def

1, BIKE|CAR|TRUCK, GREY|BLUE|RED

2, TRUCK|BIKE|CAR, VIOLET|ORANGE|YELLOW

3, CAR|TRUCK|BIKE, BLACK|WHITE|GREEN

];

Re: Index() and SubStringCount() functions

effinty2112 — Fri, 28 Jul 2017 14:24:15 GMT

Sunny,

That's beautiful - you are the Mozart of QlikView scripting!

Re: Index() and SubStringCount() functions

sunny_talwar — Fri, 28 Jul 2017 14:26:16 GMT

Hahahaha

Thanks effinty2112‌!!

Re: Index() and SubStringCount() functions

bmensing — Fri, 28 Jul 2017 14:52:51 GMT

Well that wasn't that hard...

Thank u so much!!!

Re: Index() and SubStringCount() functions

antoniotiman — Fri, 28 Jul 2017 15:05:13 GMT

Another way without recors increased

Regards,

Antonio

Re: Index() and SubStringCount() functions

effinty2112 — Fri, 28 Jul 2017 15:12:11 GMT

Hi Bernd / Sunny,

This is just a bit of fun on a Friday afternoon. Without scripting we can get:

=ValueLoop(1,3)	abc	=if(SubField(abc,'\|',ValueLoop(1,3))='BIKE','BIKE')	=if(SubField(abc,'\|',ValueLoop(1,3))='BIKE',SubField(def,'\|',ValueLoop(1,3)))
1	BIKE\|CAR\|TRUCK	BIKE	GREY
2	TRUCK\|BIKE\|CAR	BIKE	ORANGE
3	CAR\|TRUCK\|BIKE	BIKE	GREEN

This isn't as flexible as Sunny's script solution. Just for ha ha's.

cheers

Andrew

Re: Index() and SubStringCount() functions

antoniotiman — Fri, 28 Jul 2017 15:18:08 GMT

Without Change in Script

Re: Index() and SubStringCount() functions

bmensing — Fri, 28 Jul 2017 16:50:31 GMT

Hey Andrew,

i got you guys... expecially for a friday afternoon its hard to concentrate on issues like that. Your solution is far better for my case!!

Re: Index() and SubStringCount() functions

bmensing — Mon, 31 Jul 2017 15:11:37 GMT

another good one, thanks!