How to create a flag combination of two values in load script

[ashis]

Hello,

I want to create a flag in load script based on cities, so I am using this below syntax 

Basically , when the flag is 1 then  city Boston and NY should be selected and when flag is 2 then Boston and Florida should be selected.

seems like syntax is incorrect and I am getting no value of this CityFlag.

dual(City, pick(match(City, 'Boston' AND 'NY', 'Boston' AND 'Florida' ), 1, 2)) as CityFlag

Please help.

[micheledenardi]

The syntax you're using is completely wrong. Please take a look on how Dual(), Pick() and Match() functions works.

Can you have multiple values in "City" in the same record? If yes, probably you have to use SubStringCount(),  if not you have to group your data or use infra record functions.

Please share an example of dataset so we can help you.

[ashis]

Hi Micheledenardi, thank you for your reply. 

Yes we can have multiple values of City field.

I also tried below script , seems still not working.

City:
load*,

Pick(WildMatch(City, 'Boston|NY', 'Boston|Florida'), 1, 2) as CityFlag;

LOAD * INLINE [
City, Sales
Boston, 5000
CA, 3000
Florida, 1000
NY, 6000
Huston, 30
Baltimore, 500
];

[micheledenardi]

Wildmatch needs * (asteriscs) to works, not | pipelines.

In any case, based on your data, you have only 1 city per record, so is not clear what you're trying to achieve...

What you want to obtain?

Please share your expected result.

[ashis]

I want to create a flag called CityFlag, that should have two values (1 and 2),later i want to use that flag to toggle.

Now if I create a list box of CityFlag, it should show two values that is 1 and 2 . Then when I click on the 1 , two values from the City field should be selected that is Boston and NY.

Thank you,

[vinieme12]

You need to create a new table linked to your Main Table

CityGrouping

Load * Inline [

City,Flag

Boston,1

NY,1

Boston,2

Florida,2

];

Assuming you are dealing with normalized data your City field cannot have more than one value , so doing an IF() condition is meaningless

for example

Temp:

Load * , if(City='Boston',1,else ??????)   

if () will exit after the first True condition  so even if you find boston in one row you can only assign it either 1 or 2

Since its a one to many join, you have to create a new table associated to your main table

Inline [

Dim1,MEasure1,City

A,100,Boston

B,100,Florida

C,100,NY

D,100,NY

E,100,Florida

];

[micheledenardi · Accepted solution]

Fact:
LOAD * INLINE [
City, Sales
Boston, 5000
CA, 3000
Florida, 1000
NY, 6000
Huston, 30
Baltimore, 500
];


FlagTable:
Load Distinct
	City,
    1 as CityFlag
Resident Fact
	Where Match(City,'Boston','NY');

Concatenate(FlagTable)
Load Distinct
	City,
    2 as CityFlag
Resident Fact
	Where Match(City,'Boston','Florida');

[ashis]

Thank you for your reply and your thoughts.

[vinieme12]

@micheledenardi @ashis 

It's overkill to do a resident load just to group values 

Just loading your groupings is enough 

CityGrouping

Load * Inline [

City,Flag

Boston,1

NY,1

Boston,2

Florida,2

];