Predefine field values without Hard coding

[richard24best]

Hi All,

I want to load all the field values from excel for each field without hardcoding so that i can create a exception report whenever there is wrong data entry.

if(match("Manual Process Type (M)",'Process','Control')=0,'Manual Process Type (M)') as contentexception1,

if(match("Client Tier",'Strategic / 1','Focus / 2','Transactional / 3')=0,'Client Tier') as contentexception2

The reason being in future the field values can increase in the future.

Appreciate your help in advance

Regards,

Richard

[rwunderlich]

I take it you want to keep the allowable values in an external file. I've demonstrated here using inline loads instead of excel, but hopefully it communicates the idea. 

Ctx1:
LOAD * Inline [
context1Value
A
B
];
Ctx2:
Load * Inline [
context2Value
Zed
Go
There
];

Data:
LOAD 
  [Process Type],
  [Client Tier],
  If(not Exists(context1Value, [Process Type]), 'Process Type', '') as ContentException1,
  If(not Exists(context2Value, [Client Tier] ), 'Client Tier', '') as ContentException2 
Inline [
Process Type, Client Tier
A, kk
B, Go
C, There
D, What
E, argh
];

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[richard24best · Accepted solution]

Thanks for your response, well i didnt wanted to hard code the values.

Anyway i was able to figure out as follows - 

[Mapping_Manual Process Type1]:
Mapping Load "Manual Process Type1",'Y' RESIDENT Static;

NoConcatenate
ValidateContent:
Load SqNo,
if(ApplyMap('Mapping_Manual Process Type1',"Manual Process Type (M)",'N') = 'N','Manual Process Type (M)') as contentexception1