Index() and SubStringCount() functions

[bmensing]

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 🙂

[sunny_talwar · Accepted solution]

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

];

[effinty2112]

Sunny,

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

[sunny_talwar]

Hahahaha

Thanks effinty2112‌!!

[bmensing]

Well that wasn't that hard...

Thank u so much!!!

[antoniotiman]

Another way without recors increased

LOAD *,SubField(def,'|',SubStringCount(Left(abc,Index(abc,'BIKE')),'|')+1) as Color
Inline [
# abc def
1 BIKE|CAR|TRUCK GREY|BLUE|RED
2 TRUCK|BIKE|CAR VIOLET|ORANGE|YELLOW
3 CAR|TRUCK|BIKE BLACK|WHITE|GREEN
](delimiter is spaces);

Regards,

Antonio

[effinty2112]

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

[antoniotiman]

Without Change in Script

[bmensing]

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!!

[bmensing]

another good one, thanks!