Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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
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
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!
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
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
Hi Jason,
Thanks a ton.. It worked...
Warm Regards,
Sandeepa