i want to create a derived columns from an existing column

Question

i have 3 variables CUSTOMER ID, MONTH and PRODUCT for 6 months of data.

i want to create the current month VARIABLE for each customer as CURRENT MONTH and previous month variable as PREVIOUS MONTH.

then product for current month as CM_PRODUCT and previous month as PM_PRODUCT.

I have used this code in SQL but don't know how to replicate it in here

EXTRACT(YEAR FROM TO_DATE(MONTH_YEAR,'DD/MM'))='2018' THEN EXTRACT(MONTH FROM TO_DATE(MONTH_YEAR,'DD/MM'))

WHEN EXTRACT(YEAR FROM TO_DATE(MONTH_YEAR,'DD/MM'))='2019' AND EXTRACT(MONTH FROM TO_DATE(MONTH_YEAR,'DD/MM'))=1

THEN 13 ELSE 0 END AS CURRENT_MONTH,

CASE WHEN

EXTRACT(YEAR FROM TO_DATE(MONTH_YEAR,'DD/MM'))='2018' THEN EXTRACT (MONTH FROM TO_DATE (MONTH_YEAR,'DD/MM')) - 1

WHEN EXTRACT(YEAR FROM TO_DATE(MONTH_YEAR,'DD/MM))='2019' AND EXTRACT(MONTH FROM TO_DATE(MONTH_YEAR,'DD/MM'))=1

THEN 12 ELSE 0

END AS PREVIOUS_MONTH

MichalM · Accepted Answer

@nidah5

You can see all DateTime function available within Alteryx here.

These are the ones I'd focus on

DateTimeNow() - returns date and time when the function is executed

DateTimeFormat - allows you to extract year, month, day etc from a date; DateTimeFormat(DateTimeNow(), "%m") will return month number for example. See Specifiers within the help pages for more details.

DateTimeAdd - allows you to perform date calculations DateTimeAdd(DateTimeNow(),-1,"month") will subtract one month from the current date.

MichalM · Accepted Answer

@nidah5

Please see the attached example.

1. You convert your date into yyy-mm-dd format using the DateTime tool

2. Use the formula tool to extract the year and month and build your conditional

nidah5 · Accepted Answer

thank you , this helps.

i have one more query.

i want to create 2 columns,

1. current month number of products(cm_no_products)

2. previous month number of products(pm_no_products)

this will be done by doing left join on same table

please find my SQL code foe same.

i am not able to figure out same in alteryx

SELECT CM_CUSTOMER_ID, CM_MONTH_YEAR, CM_CURRENT_MONTH, CM_PREVIOUS_MONTH, CM_NO_OF_PRODUCTS, PM_NO_OF_PRODUCTS

FROM (
SELECT CM.CUSTOMER_ID AS CM_CUSTOMER_ID,
CM.MONTH_YEAR AS CM_MONTH_YEAR,
CM.CURRENT_MONTH AS CM_CURRENT_MONTH,
CM.PREVIOUS_MONTH AS CM_PREVIOUS_MONTH,
CM.NO_OF_PRODUCTS AS CM_NO_OF_PRODUCTS,
PM.NO_OF_PRODUCTS AS PM_NO_OF_PRODUCTS

FROM POSTPAID_CUSTOMER CM LEFT OUTER JOIN POSTPAID_CUSTOMER PM
ON CM.CUSTOMER_ID = PM.CUSTOMER_ID AND CM.PREVIOUS_MONTH = PM.CURRENT_MONTH)

MichalM · Accepted Answer

@nidah5

Have a look at the documentation of the Join tool available here. The below is how you perform a Left join in Alteryx - Union the output of L (Left Un-joined) and J (Inner Join)