Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

markgraham123
Not applicable

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

Re: If Condition

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;

11 Replies
sunny_talwar
Not applicable

Re: If Condition

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

markgraham123
Not applicable

Re: If Condition

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

Re: If Condition

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;

promaniuk
Not applicable

Re: If Condition

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

markgraham123
Not applicable

Re: If Condition

Exactly sunny

You nailed it again.

Thank you very much man!!

markgraham123
Not applicable

Re: If Condition

Sunny, can i please know what does the final statement

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

Just eager to learn

sunny_talwar
Not applicable

Re: If Condition

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

Re: If Condition

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

Re: If Condition

So, it works same like

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