Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

If Condition

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.

1 Solution

Accepted Solutions
sunny_talwar

This?

Capture.PNG

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;

View solution in original post

11 Replies
sunny_talwar

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

markgraham123
Specialist
Specialist
Author

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

This?

Capture.PNG

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
Not applicable

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

markgraham123
Specialist
Specialist
Author

Exactly sunny

You nailed it again.

Thank you very much man!!

markgraham123
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

So, it works same like

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