topic Matching Fields in QlikView

Matching Fields

Tue, 06 Sep 2011 18:07:32 GMT

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..

Matching Fields

Jason_Michaelides — Tue, 06 Sep 2011 19:33:32 GMT

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

Matching Fields

Jason_Michaelides — Tue, 06 Sep 2011 19:34:56 GMT

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!

Re: Matching Fields

Tue, 06 Sep 2011 20:01:35 GMT

Like For example :

Personcode	OrgCode	Service
1	10	aa
2	20	bb
3	30	cc
4	40	dd
5	50	ee
6	60	ff

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

Matching Fields

Jason_Michaelides — Tue, 06 Sep 2011 21:36:46 GMT

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

Re: Matching Fields

Wed, 07 Sep 2011 08:31:42 GMT

Hi Jason,

Thanks a ton.. It worked...

Warm Regards,

Sandeepa