topic Re: How to separate the column having multiple data types in qlikview in QlikView

How to separate the column having multiple data types in qlikview

Anonymous — Tue, 17 Jan 2017 14:53:01 GMT

Hi,

I am getting a column from the database which has got some text(description) and some telephone numbers like one starts with +44.... and some numbers starts with 07......

How can I get only +44 numbers into the chart which is my requirement.

Any help is much appreiciated.

regards,

Vamsi,

Re: How to separate the column having multiple data types in qlikview

sunny_talwar — Tue, 17 Jan 2017 14:59:37 GMT

May be using a set analysis like this:

Sum({<[Telephone Number] = {'+44*'}>}Measure)

Re: How to separate the column having multiple data types in qlikview

Anonymous — Tue, 17 Jan 2017 15:07:36 GMT

Hi sunny,

What is this measure?

I dont understand as i am new to set analysis.

Re: How to separate the column having multiple data types in qlikview

sunny_talwar — Tue, 17 Jan 2017 15:12:24 GMT

Measure is a placeholder for your actual field which you are aggregating. Since you have not shared where and how you are trying to do this, I just gave you a very generic expression.... So if you expression is Sum(Sales), add the set analysis part to make it this:

Sum({<[Telephone Number] = {'+44*'}>} Sales)

Re: How to separate the column having multiple data types in qlikview

girirajsinh — Tue, 17 Jan 2017 15:14:09 GMT

Sum({< dimension = {"+44*"}>} Fact_measure)

Re: How to separate the column having multiple data types in qlikview

Anonymous — Tue, 17 Jan 2017 15:17:12 GMT

Just a minute, i will try to share that.

Re: How to separate the column having multiple data types in qlikview

Anonymous — Tue, 17 Jan 2017 15:21:02 GMT

Re: How to separate the column having multiple data types in qlikview

sunny_talwar — Tue, 17 Jan 2017 15:23:20 GMT

Where is your chart my friend? What is the expression your planned to use for your chart?

Re: How to separate the column having multiple data types in qlikview

Anonymous — Tue, 17 Jan 2017 15:24:29 GMT

Yes , the requirement i have is the Table should display the telephone numbers that starts with +44 if the user select message type as 'shortmessageendpoint ' for various 'org_name' .

You can see the 'Messageend point' list box has telephone numbers and some text

hope it makes sense.

Re: How to separate the column having multiple data types in qlikview

Anonymous — Tue, 17 Jan 2017 15:29:40 GMT

apologise. its a straight table. Here you go..I need to display only those +44 numbers in the message endpoint s column in the straight table.

Re: How to separate the column having multiple data types in qlikview

sunny_talwar — Tue, 17 Jan 2017 15:32:11 GMT

May be like this

Concat({<[Message Endpoint] = {'+44*'}>} [Message Endpoint])

or add [Message Endpoint] as dimension and then this as your expression

Avg({<[Message Endpoint] = {'+44*'}>} 1)

Re: How to separate the column having multiple data types in qlikview

luke1986 — Tue, 17 Jan 2017 15:39:33 GMT

Put this in Load Script:

...

if(left(trim(Message_Endpoint),3)='+44',Message_Endpoint,null()) as Message_Endpoint

....

then you only get all +44 Phone numbers.

Re: How to separate the column having multiple data types in qlikview

Anonymous — Tue, 17 Jan 2017 15:55:32 GMT

Thank you .It did give me only those +44 numbers.

Re: How to separate the column having multiple data types in qlikview

Anonymous — Tue, 17 Jan 2017 15:56:26 GMT

Thanks sunny.

It is pulling the 44 numbers but some weird behavior is throwing on the table.

cant understand what am i doing wrong.