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: 
Not applicable

Matching Fields

Hi All,

I am using Qlikview10.

In my table there is a field called Service. Also I am loading one excel sheet which has only one field called Service.

What I have to do is I have to compare Table.Service to Service(excel-sheet).

If the value is matched in both the service field is then it is valid inclusion or else Valid Exclusion.

Please suggest how to achieve it..

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Almost exactly as I said then:

//first load the mapping table from the Excel file

Map_Service:

MAPPING LOAD

Service,

'Included'

FROM ...excel file;

//then load main data and use ApplyMap() to see if it exists in the Excel file

Data:

LOAD

Personcode,

OrgCode,

Service,

ApplyMap('Map_Service',Service,'Excluded') AS [Included?]

FROM ...data source;

That should achieve what you want.

Jason

View solution in original post

5 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

I think I'd use a mapping table.  In your script:

//first load the mapping table from the Excel file

Map_Service:

MAPPING LOAD

Service,

'Match!'

FROM ...excel file;

//then load main data and use ApplyMap() to see if it exists in the Excel file

Data:

LOAD

*,

ApplyMap('Map_Service',Service,'No match!') AS [Valid service?]

FROM ...data source;

That should do it.  Look up ApplyMap() in the F1 help for more explanation.

Hope this helps,

Jason

Jason_Michaelides
Luminary Alumni
Luminary Alumni

PS: this is one-way checking. It assumes you don't have any Service values in the Excel file that are not in your main data.  If this is not the case there are other solutions!

Not applicable
Author

Like For example :

Personcode
OrgCode
Service
110aa
220bb
330cc
440dd
550ee
660ff

Service File(Excel File)
aa
bb
ee

What I have to do is I have to comapre Service field to Service File(Excel File) and assign the respective personcode,orgcode  as inclusion or exclusion.

Like here Personcode 3,4,6 are excluded.

Please suggest how to resolve it...

Thx,

Sandeepa

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Almost exactly as I said then:

//first load the mapping table from the Excel file

Map_Service:

MAPPING LOAD

Service,

'Included'

FROM ...excel file;

//then load main data and use ApplyMap() to see if it exists in the Excel file

Data:

LOAD

Personcode,

OrgCode,

Service,

ApplyMap('Map_Service',Service,'Excluded') AS [Included?]

FROM ...data source;

That should achieve what you want.

Jason

Not applicable
Author

Hi Jason,

Thanks a ton.. It worked...

Warm Regards,

Sandeepa