topic Re: Substring in String in QlikView

Substring in String

Sun, 31 Jan 2016 17:28:18 GMT

Hi All,

Greetings for the day!

I have one variable which contains value 'India-1.Hyderabad,India-2.Bangalore,India-3.Kolkata,US-1.NewYork,US-2.Washington'

I want to create one more variable with india related values like 1.Hyderabad,2.Bangalore,3.Kolkata

Could you please help me here

Thanks,

Chiru

Re: Substring in String

sunny_talwar — Sun, 31 Jan 2016 17:39:15 GMT

It is field we are talking about or a variable? If it is a variable how this getting created in a first place?

Re: Substring in String

Gysbert_Wassenaar — Sun, 31 Jan 2016 17:56:11 GMT

SubField( MyVariableName, '-', 1) as Country

SubField( MyVariableName, '-', -1) As City

Re: Substring in String

swuehl — Sun, 31 Jan 2016 18:19:56 GMT

If you are really using only variables, maybe like attached?

Re: Substring in String

sunny_talwar — Sun, 31 Jan 2016 18:33:11 GMT

I really like the Valueloop technique, but I have feeling that if this truly a variable, there might be an easier way to create the second variable without using the first one.

Re: Substring in String

swuehl — Sun, 31 Jan 2016 18:35:48 GMT

Which variable do you want to dismiss?

I just used a variable for India to create a more flexible filter, of course you can use a constant value for 'India'.

Re: Substring in String

sunny_talwar — Sun, 31 Jan 2016 18:40:14 GMT

Don't really want to dismiss anything from what you have proposed. It is an amazing solution.

What I am suggesting is that, if this variable is being getting created in the script using Peek() function. There might be an easier way to calculate another variable with the required output in the script. It won't be dynamic as yours (using vB), but may be we can have pre-created variables for each Country. Do you get what I am trying to say?

Re: Substring in String

sunny_talwar — Sun, 31 Jan 2016 18:48:43 GMT

This is what I meant Stefan

Table:

LOAD Concat(Country& '-'&No.&'.'&City, ',') as Var1,

Concat(If(Country = 'India', No.&'.'&City), ',') as Var2,

Concat(If(Country = 'US', No.&'.'&City), ',') as Var3;

LOAD * Inline [

Country, No., City

India, 1, Hyderabad,

India, 2, Bangalore,

India, 3, Kolkata,

US, 1, NewYork,

US, 2, Washington

];

LET vVar1 = Peek('Var1');

LET vVar2 = Peek('Var2');

LET vVar3 = Peek('Var3');

Re: Substring in String

swuehl — Sun, 31 Jan 2016 19:09:30 GMT

Ok,

if the long string originates from your data model, I guess there are many easier solutions than what I suggested.

Re: Substring in String

sunny_talwar — Sun, 31 Jan 2016 19:14:55 GMT

Yup that is what I was hinting towards.