topic Re: How to Find Matching Numbers between two Columns in QlikView

How to Find Matching Numbers between two Columns

Thu, 23 Apr 2015 07:50:28 GMT

Hi,

Please assist i need to match Cell Numbers between two Columns, but the problem is

one column starts with country code 27

e.g

Column A

0738257934

the other one is

Column B

27738257934

how do i match the two in Set Analysis?

Thanks.

Re: How to Find Matching Numbers between two Columns

morganaaron — Thu, 23 Apr 2015 08:00:11 GMT

Would it not be easier to simply change one in the script when you're loading it, or do you not want these to be linked fields?

Re: How to Find Matching Numbers between two Columns

Thu, 23 Apr 2015 08:02:18 GMT

Hi,

Please send me your Method that you Prefer

Regards.

Re: How to Find Matching Numbers between two Columns

jagan — Thu, 23 Apr 2015 08:23:21 GMT

Hi,

Try like this in script

LOAD

If(ColumnA=Mid(ColumnB, 2), 1 0) AS Flag

FROM DataSource;

Now in frontend you can use like

=Sum({<Flag={1}>} MeasureName)

Hope this helps you.

Regards,

Jagan.

Re: How to Find Matching Numbers between two Columns

prieper — Thu, 23 Apr 2015 08:23:42 GMT

IF(LEFT(TelephoneNumber, 2) = '27', '0' & MID(TelephoneNumber, 3, LEN(TelephoneNumber) - 2, TelephoneNumber)

Peter

Re: How to Find Matching Numbers between two Columns

morganaaron — Thu, 23 Apr 2015 08:36:31 GMT

I'd renumber the second field. It depends on where these fields come from, but if you simply wanted to change B on load, you could write:

'0' & Mid(ColumnB, 3) as ColumnA

This would join your tables on this field however. If you don't want that to happen, you can either use Jagan's script below, or you can load as ColumnB and use an applicable statement in the frontend.

Re: How to Find Matching Numbers between two Columns

jagan — Thu, 23 Apr 2015 08:45:57 GMT

HI,

Try this

Hi,

Try like this in script

LOAD

If(Text(ColumnA)= '0' & Mid(ColumnB, 2), 1 0) AS Flag

FROM DataSource;

Now in frontend you can use like

=Sum({<Flag={1}>} MeasureName)

Hope this helps you.

Regards,

Jagan.

Re: How to Find Matching Numbers between two Columns

buzzy996 — Thu, 23 Apr 2015 08:59:27 GMT

use this,

=If(ColumnA='0'& right(ColumnB,9),'Matched','UnMatched')

Re: How to Find Matching Numbers between two Columns

Thu, 23 Apr 2015 09:20:46 GMT

Hi, where will apply this to?

Expression or Load Script?

Re: How to Find Matching Numbers between two Columns

Thu, 23 Apr 2015 09:29:11 GMT

Isn't possible to apply it on the Front End, set analysis?

Re: How to Find Matching Numbers between two Columns

jagan — Thu, 23 Apr 2015 09:40:43 GMT

Hi,

Try like this

Sum(Aggr(If(Text(ColumnA)= '0' & Mid(ColumnB, 2), Sum(Sales)), ColumnA, ColumnB))

Hope this helps you.

Regards,

Jagan.

Re: How to Find Matching Numbers between two Columns

buzzy996 — Thu, 23 Apr 2015 09:41:24 GMT

if it's front end u can use this directly.

=If(ColumnA='0'& right(ColumnB,9),'Matched','UnMatched')

if it's back end u can use this,

If(ColumnA='0'& right(ColumnB,9),'Matched','UnMatched') as Flag

else ur looking u want to see this only with set expressions?any reason.

Re: How to Find Matching Numbers between two Columns

Thu, 23 Apr 2015 11:09:14 GMT

HI,

it works but how do i exclude Matched from the Field as i uised a List to create a new Column

Regards.

Re: How to Find Matching Numbers between two Columns

buzzy996 — Thu, 23 Apr 2015 11:18:18 GMT

okie,wht u want to show on new column?

tht new column want to create when both are matched?

u can just replace what u want to see when it's matched,on the above expression..u can place any text!