If Condition

[markgraham123]

Hi all,

I'm stuck at a point where i have ID and other filed CHDLM

I wanna add a new dimension which says 'X' category, if an ID has 'X' in its CHDLM field.

In the end, i wanna remove all the CHDLM fileds with 'X'. So, i can see whether the ID belongs to 'X' category or not.

Please find the attached application.

Any help is highly appreciated.

[sunny_talwar]

ID 1 has one CHDLM with X and one without X. Would you want to see it or not?

[markgraham123]

Hi Sunny,

ID 1 has one CHDLM with X and one without X.

I wanna add a new dimension 'Comments' .

In the comments dimension, i will have 'X category' for ID1.

In the final resident load, i will just take Load * Resident where Right(CHDLM, 1) <> 'X,

So, i will have only CHDLM without 'X' but in the category, i can see that the ID 1 was of 'X' Category.

[sunny_talwar · Accepted solution]

This?

Table:

LOAD * INLINE [

    ID, CHDLM

    1, 20151111061X

    1, 20151110261

    2, 112323

    3, 3435435

    4, 434342X

    4, 586785656X

];

Join(Table)

LOAD Count(ID) as X_Count,

  ID

Resident Table

Where Len(Trim(KeepChar(CHDLM, 'X'))) > 0

Group By ID;

FinalTable:

NoConcatenate

LOAD *,

  If(X_Count > 0, 'Had X') as Category

Resident Table

Where Len(Trim(KeepChar(CHDLM, 'X'))) = 0;

DROP Table Table;

[Anonymous]

Hi, i send you an example. Tell me if is that what you wanted.

[markgraham123]

Exactly sunny

You nailed it again.

Thank you very much man!!

[markgraham123]

Sunny, can i please know what does the final statement

Where Len(Trim(KeepChar(CHDLM, 'X'))) = 0; does.

Just eager to learn

[sunny_talwar]

This is saying from each row of data keep just X (it can only keep X, if it is there) and if there is X kept then the len > 1. If no X kept then len = 0

I hope it makes sense

[markgraham123]

Hi Pablo,

That what i'm looking for.

With your application, i learnt how to do it on front end.

Thanks a lot Pablo

[markgraham123]

So, it works same like

Where Right(CHDLM,1)<>'X';