Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
pra_kale
Creator III
Creator III

How to search word in a sentence

Hi,

I have attached Two excel files below..

1 file contains the complete address in column 5.

2  file contains address in a 3 columns.

How i can search these 3 columns containing word in a address 5. It means if any word from that 3 columns is found in the complete address then in final output column remark should be Yes.

       

CHDRNUMName_1Name_2Name_3Name_4Name_5Final output
12345678RamnagarShiv Mandir RoadGhoad Bundar RoadThane WMaharshtraYes
9876774Phadke roadSadanand PalaceJoshiCSTMaharshtraYes
5631233ABCCDEEFGHIJAndhra Pradesh
9876543MatungaZZZAAATitwalaNasikYes
9996633ZXCDFCQWEAXCZZZ

       

Please help to resolve the issue.

Thanks in Advance.

1 Solution

Accepted Solutions
sunny_talwar

May be this

Mapping1:

Mapping

LOAD Address_1,

  '^Yes^' as Flag1

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

Mapping2:

Mapping

LOAD Address_2,

  '^Yes^' as Flag2

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

Mapping3:

Mapping

LOAD Address_3,

  '^Yes^' as Flag3

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

A:

LOAD CHDRNUM,

    Name_1,

    Name_2,

    Name_3,

    Name_4,

    Name_5

FROM

[Details_Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

B:

LOAD *,

  PurgeChar(RangeMaxString(If(Len(Trim(Flag1)) = 0, 'Z', Flag1), If(Len(Trim(Flag2)) = 0, 'Z', Flag2), If(Len(Trim(Flag3)) = 0, 'Z', Flag3)), 'Z') as Flag;

LOAD *,

  TextBetween(MapSubString('Mapping1', complete_Address), '^', '^') as Flag1,

  TextBetween(MapSubString('Mapping2', complete_Address), '^', '^') as Flag2,

  TextBetween(MapSubString('Mapping3', complete_Address), '^', '^') as Flag3;

LOAD *,

  Name_1 &'|'& Name_2 &'|'& Name_3 &'|'& Name_4 &'|'& Name_5 as complete_Address

Resident A;

DROP Table A;

DROP Fields Flag1, Flag2, Flag3;

View solution in original post

8 Replies
sunny_talwar

May be this?

B:

CrossTable(Address, Data)

LOAD 1 as Dummy,

  Address_1,

    Address_2,

    Address_3

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

MappingTable:

Mapping

LOAD Data,

  '/Yes\' as Flag

Resident B;

DROP Table B;

A:

LOAD CHDRNUM,

    Name_1,

    Name_2,

    Name_3,

    Name_4,

    Name_5

FROM

[Details_Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

B:

LOAD *,

  TextBetween(MapSubString('MappingTable', complete_Address), '/', '\') as Flag;

LOAD *,

  Name_1 &'|'& Name_2 &'|'& Name_3 &'|'& Name_4 &'|'& Name_5 as complete_Address

Resident A;

DROP Table A;

pra_kale
Creator III
Creator III
Author

Hi,

Thanks sunny for your help...it is working fine.

But, in actual data i have special characters in the address like Ramanagar/18..etc. sample file is attached for reference so if match found then in flag column i am getting out-put like 17/|/Yes  where as i only want Yes.

Secondly only change required is, if all the  3 words in the 3 columns are found in the complete address then only flag should be Yes. So i will get Yes in front of only 2 policy numbers as per attached sample.

    

CHDRNUMName_1Name_2Name_3Name_4Name_5
12345678Ramnagar/16Town HallKOLHAPURThane WMaharshtra
9876774Phadke roadSadanand Palace/17/JoshiThane WMaharshtra

Thanks in advance.

sunny_talwar

Try with a special character which you won't have in your address field

B:

CrossTable(Address, Data)

LOAD 1 as Dummy,

    Address_1,

    Address_2,

    Address_3

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

MappingTable:

Mapping

LOAD Data,

  '^Yes^' as Flag

Resident B;

DROP Table B;

A:

LOAD CHDRNUM,

    Name_1,

    Name_2,

    Name_3,

    Name_4,

    Name_5

FROM

[Details_Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

B:

LOAD *,

  TextBetween(MapSubString('MappingTable', complete_Address), '^', '^') as Flag;

LOAD *,

  Name_1 &'|'& Name_2 &'|'& Name_3 &'|'& Name_4 &'|'& Name_5 as complete_Address

Resident A;

DROP Table A;

pra_kale
Creator III
Creator III
Author

Hi Sunny,

great it is working..

But only one change is required in the application that is if all the conditions are satisfied then only it will give Yes in flag column means it will search all the three words from Address file and if all these tree words are exist in the complete address column then only flag should be Yes.

Thanks in advance.

sunny_talwar

May be this

Mapping1:

Mapping

LOAD Address_1,

  '^Yes^' as Flag1

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

Mapping2:

Mapping

LOAD Address_2,

  '^Yes^' as Flag2

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

Mapping3:

Mapping

LOAD Address_3,

  '^Yes^' as Flag3

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

A:

LOAD CHDRNUM,

    Name_1,

    Name_2,

    Name_3,

    Name_4,

    Name_5

FROM

[Details_Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

B:

LOAD *,

  PurgeChar(RangeMaxString(If(Len(Trim(Flag1)) = 0, 'Z', Flag1), If(Len(Trim(Flag2)) = 0, 'Z', Flag2), If(Len(Trim(Flag3)) = 0, 'Z', Flag3)), 'Z') as Flag;

LOAD *,

  TextBetween(MapSubString('Mapping1', complete_Address), '^', '^') as Flag1,

  TextBetween(MapSubString('Mapping2', complete_Address), '^', '^') as Flag2,

  TextBetween(MapSubString('Mapping3', complete_Address), '^', '^') as Flag3;

LOAD *,

  Name_1 &'|'& Name_2 &'|'& Name_3 &'|'& Name_4 &'|'& Name_5 as complete_Address

Resident A;

DROP Table A;

DROP Fields Flag1, Flag2, Flag3;

View solution in original post

pra_kale
Creator III
Creator III
Author

Hi,

Great Sunny it is working..

Just want to know whether it is searching  complete words in a particular column for e.g. in a Address_1 column of Address file i have "Town Hall" and " Joshi Marg" then it will search both this words together and if found then only it will give flag as Yes or it is searching "Town" separately and "Hall" separately and if found any where then give Yes.

Secondly whether it possible to remove case sensitive thing from the coding because in Details_Data file if "Town Hall" is written as "TOWN HALL'" then it is not giving Yes flag.

Thanks in advance.

sunny_talwar

1) Town Hall will always be searched together if they are single word. It won't look for Town and then Hall

2) You can use Upper or Lower or Capitalize functions to both Address and the checking fields so that they all have the same casing.

pra_kale
Creator III
Creator III
Author

Thanks Sunny...Great..

It is working..