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.

thnkgreen
Contributor

Filter results of column alias in SELECT statement

Hello my friends.  I have been unable to figure out this problem.  I am trying to filter the SAPMERCHVDIVISIONCODE alias in the statement below.  I only want data with a value of 806 in this column.  Is it possible to do within the same statement?  If not, what would the separate statement look like.  Thanks so much!!

SQL Select

    PSPLN As TASK,

    PSCASN,

    PSSEA,

    PSLTYP,

    PSAREA,

    PSZONE,

    PSAISL,

    PSBAY,

    PSLEVL,

    PSPOSN,

    PSSYR,

    PSSTYL,

    PSSSFX,

    CAST(LEFT(PSSYR || PSSTYL || PSSSFX,3) AS INTEGER) AS SAPMERCHDIVISIONCODE,

    PSSYR || PSSTYL || PSSSFX AS SKU,

    PSSZDS,

    PSSQTY,

    PSQPUL,

    PSRLOC,

    PSSTAT,

    PSDCR,

    PSCRT,

    PLWHSE,

    CASE PLWHSE

        WHEN '001'

            THEN TRIM(PSAREA) || TRIM(PSZONE) || Left(TRIM(PSAISL),1) || Right(TRIM(PSAISL),1) || TRIM(PSBAY)

        WHEN '003'

            THEN TRIM(PSAISL) || TRIM(PSLEVL) || TRIM(PSPOSN)

        ELSE TRIM(PSAISL) || TRIM(PSLEVL) || TRIM(PSPOSN)

    END AS RESERVELOCATION

    From PKMANH221D.PSPULL00 A, PKMANH221D.PLPULL00 B

Where A.PSPLN = B.PLPLN

And A.PSCRT ='1'

And A.PSDCR >= $(zconvDate)     

Order By A.PSPLN;

4 Replies
thnkgreen
Contributor

Re: Filter results of column alias in SELECT statement

I should have added that this SELECT statement is written for DB2 as it is going out onto the server to retrieve data.

calvindk
Contributor III

Re: Filter results of column alias in SELECT statement

just add

AND  CAST(LEFT(PSSYR || PSSTYL || PSSSFX,3) AS INTEGER) = 806

thnkgreen
Contributor

Re: Filter results of column alias in SELECT statement

Thank you for the quick response Anders. Unfortunately this does not work for some reason. I thought if I added “AS SAPMERCHDIVISIONCODE” to the end of the statement that you provided it might work, but the word AS is not recognized.

calvindk
Contributor III

Re: Filter results of column alias in SELECT statement

Sorry then, I don't know DB2.

It should work SQL wise.

Alternatively you can do the filtering in Qlikview by adding

Temp:

SQL Select part

TableName:

Load *, 1 as Diff

Resident Temp

Where SAPMERCHDIVISIONCODE = 806;

Drop Table Temp;

Drop Field Diff;

But this is a wasteful approach.

Best wishes

Community Browser