Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI all,
This might look simple, but am stuck here. Have a data set like this below -
Name | Country |
---|---|
Chang | Japan |
Ashok | USA |
Ashok | India |
Ashok | Qatar |
John | Australia |
Mike | UK |
Ross | UK |
Glen | Germany |
Bill | USA |
David | UK |
Xiomi | China |
Ravi | India |
Using a load script expression to create an unique flag like this below-
if(match(Country, ‘India’) , ‘Y’ ,
if(match(Country, ‘UK’) , ‘N’
)) as Flag
Need a data set result as below -
Name | Flag |
---|---|
Chang | N |
Ashok | Y |
Ravi | Y |
John | N |
Mike | N |
Ross | N |
Glen | N |
Bill | N |
David | N |
Xiomi | N |
IMPORTANT- I don’t need a flag value as N for Ashok and Ravi. My above script is actually generating N value and I don’t need that.
How can I tweak the script?
Not sure I follow. I see the following;
From script;
data:
Load
if(match(Country, 'India') , 'Y' ,
if(match(Country, 'UK') , 'N'
)) as Flag,
*
;
LOAD * INLINE [
Name, Country
Chang,Japan
Ashok,USA
...
Regards,
Chris.
Try Mapping....
CountryMap:
Mapping Load * Inline [
CC, Flag
India, Y
];
Data:
LOAD
Name,
Country
, ApplyMap('CountryMap', Country, 'N') AS CFlag
FROM [lib://mypath/country.xlsx]
(ooxml, embedded labels, table is Sheet1);
Hi ,chrismarlow
Actually I don’t need NULL values in the result table for Ashok.
CAn we a avoid that in any way?
Hello,
Is that because Ashok already scores a Y because of India?
Is the rule here you want distinct names with a Y if they have India at all, N if UK & discard the rest?
Cheers,
Chris.