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_NUM | POS_LOC_CD | ARTICLE | POSTING_DT | MVMNT_TYPE | DOC_NUM | VALUATION_TYPE | DISPOSITION_CD | PROGRAM_IND | rownum |
123 | 4000050 | XX | 4/2/2018 | 653 | 14 | XX | XX | XX | 1 |
123 | 4000050 | XX | 4/2/2018 | 309 | 13 | XX | XX | XX | 2 |
123 | 4000050 | XX | 4/2/2018 | 309 | 13 | XX | XX | XX | 3 |
123 | 4000050 | XX | 4/4/2018 | 309 | 8 | XX | XX | XX | 4 |
123 | 4000050 | XX | 4/4/2018 | 309 | 8 | XX | XX | XX | 5 |
123 | 4000050 | XX | 4/4/2018 | 311 | 4 | XX | XX | XX | 6 |
123 | 4000050 | XX | 4/4/2018 | 311 | 4 | XX | XX | XX | 7 |
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
Solved! Go to Solution.
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.