comparing two fields

[#1]

Hi Guys!!!!!!!! hope all of u doing good..........

I have a column called state(i.e AP,TN,KL,KA...etc) and i have another field in the same table having multiple state values.Now i need to compare these two fields to derive another field.

For example :

                         State                Spec States    

                         AP                       TN,AP,KL        

                         KL                       GJ,DL,KA

                          DL                      DL,KA,KL

If Spec States contains State field value then result should be 1 otherwise 0.   

desired Output:

                         State                Spec States       Result

                         AP                       TN,AP,KL         1

                         KL                       GJ,DL,KA          0

                          DL                      DL,KA,KL          1

PFA and Kindly do needful help.

Reach me for more info if required.

[swuehl]

Maybe like

LOAD *,

      If( WildMatch( [Specipic States], '*'&States&'*'),1,0) as NewResult;

LOAD States,

     [Specipic States],

     Result

FROM

[POC.xlsx]

(ooxml, embedded labels, table is Sheet1);

[sunny_talwar · Accepted solution]

Couple of more options:

LOAD *,

  If(WildMatch([Specipic States], '*'&States&'*'), 1, 0) as NewResult,

If(Index([Specipic States], States), 1, 0) as NewResult2,

  If([Specipic States] LIKE '*'&States&'*', 1, 0) as NewResult3;

LOAD States,

    [Specipic States],

    Result

FROM

POC.xlsx

(ooxml, embedded labels, table is Sheet1);

[vishsaggi]

In the front end with expression in chart table as :

= IF(Match(MID(SpecStates, Index(SpecStates, State),2), State), 1,0)

[#5]

Thanks a lot buddy...!!!!!!!!!!!!!

[#6]

Great work. It's really helpful for me......

[#7]

Thanks Nagaraju.....