Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Rownum over partition by

mohdfaraz47
7 - Meteor
select  serial_num, 
    ARTICLE,
    posting_dt,
    mvmnt_type,
    doc_num,
    VALUATION_TYPE,
    DISPOSITION_CD,
    PROGRAM_IND,
    row_number() over( partition by serial_num order by posting_dt asc,doc_num asc) as ROWNUM
    FROM   scm_prd_qmvm.WSC_BI_FACT_SAP_MOVEMENTS_V
    WHERE TRIM(LEADING '0' FROM SERIAL_NUM)  IN ( Subquery follows here)

this will give me the result like this:

SERIAL_NUMPOS_LOC_CDARTICLEPOSTING_DTMVMNT_TYPEDOC_NUMVALUATION_TYPEDISPOSITION_CDPROGRAM_INDrownum
1234000050XX4/2/201865314XXXXXX1
1234000050XX4/2/201830913XXXXXX2
1234000050XX4/2/201830913XXXXXX3
1234000050XX4/4/20183098XXXXXX4
1234000050XX4/4/20183098XXXXXX5
1234000050XX4/4/20183114XXXXXX6
1234000050XX4/4/20183114XXXXXX7

 

i need this type of output in Alteryx using in db tools where i can partition the data in Serial number and can use the row num to pick up any value depending up on that. Mostly i use row num =1 and it all depends on the posting dt and doc num either being sorted by asc or desc. 

Any suggestions on this is highly appreciated 

 

 
2 REPLIES 2
rarmstrong
8 - Asteroid

You can do this with a combination or Sort tool, Formula tool and Multi-Row Tool. The workflow attached is an example.

mohdfaraz47
7 - Meteor

Thanks a lot rarmstrong. Appreciate the quick help here. Always a fan of Alteryx community. Never thought it to be this


@rarmstrong wrote:

You can do this with a combination or Sort tool, Formula tool and Multi-Row Tool. The workflow attached is an example.



simple.

Labels