Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bmensing
Contributor III
Contributor III

Index() and SubStringCount() functions

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 🙂

1 Solution

Accepted Solutions
sunny_talwar

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

];

Capture.PNG

View solution in original post

9 Replies
sunny_talwar

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

];

Capture.PNG

effinty2112
Master
Master

Sunny,

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

sunny_talwar

Hahahaha

Thanks effinty2112‌!!

bmensing
Contributor III
Contributor III
Author

Well that wasn't that hard...

Thank u so much!!!

antoniotiman
Master III
Master III

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

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)))
1BIKE|CAR|TRUCKBIKEGREY
2TRUCK|BIKE|CARBIKEORANGE
3CAR|TRUCK|BIKEBIKEGREEN

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

cheers

Andrew

antoniotiman
Master III
Master III

Without Change in Script

bmensing
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

another good one, thanks!