i want to create a derived columns from an existing column

[nidah5]

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 solution]

@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.

[caschecter]

Nidah5,

Can you provide an example of the data. Is the variable month of type 'date'? What is "product"? Does it take on values like "desk", "chair" or is it a measure of some sort.

Chuck

[nidah5]

i have date_month column with data in format of "dd/mm/yyyy'.

e.g date_month ='01/02/2018' and product is 'TV'.

I need to create a new variable extracting on bases if month and year.

if year= 2018 and month = 2 then 2 else 0 end as current month

[MichalM · Accepted solution]

@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 solution]

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 solution]

@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)