Compute new Date Column based on another Text Column

[MudCo]

Hi Team

I have 3 Columns New P Rating, Date Contract Will Expire and New Work Start Date. What I am trying to do is calculate the New Work Start Date base do the 'New P Rating' . For eg., If my

Conditions I require- If New P Rating is P4, then New Work Start date should be 2 months before the Date Contract will Expire. For P3 is 3 months before, P2 is 5 months before and P1 is 6 months before.

I Tried the below code which gave me the results in attached screenshot"

Select T1.'New P Rating',T1.Date Contract will Expire',

FORMATDATE(DATE_SUB(T1.Date Contract Will Expire',INTERVAL 60 DAY)) as 'New Work Start Date'

FROM T1

Just a note, New P Rating is a calculated column in itself in an internal macro. The actual column 'Contract Value' used for the calculation is not visible as I did not call it in my code.   

SELECT *,

CASE

WHEN ('Contract Value') <= 500000 THEN "P4"

WHEN ('Contract Value') BETWEEN 500001 AND 5000000 THEN "P3"

WHEN ('Contract Value') BETWEEN 5000001 AND 20000000 THEN "P2"

WHEN ('Contract Value') >=20000000 THEN "P1"

END AS 'New P Rating'

FROM T*

Column Date Contract will Expire is a manually entered date in a typical Confluence date style ( // )

Is there a way I can get the New Work Start Date column to show the new date as per the P rating of P1-P4 and the result calculated as per my multiple conditions mentioned above?

[Stiltsoft support · Accepted solution]

Hi @MudCo ,

Both of your queries seem to be correct and your idea about the internal and external Table Transformer macros is also correct.

The issue may be in the typo in the second query:

Select T1.'New P Rating',T1.Date Contract will Expire',
FORMATDATE(DATE_SUB(T1.Date Contract Will Expire', INTERVAL 60 DAY)) as 'New Work Start Date'
FROM T1

Not "Will" but "will" for the second time, the column is simply isn't recognized.

So, you may change the column name, add the CASE WHEN conditions and you are all set.

Also here is an example query how to combine both SELECTs in the one query and use only one Table Transformer macro:

SELECT 'New P Rating', 'Date Contract will Expire',
CASE
WHEN 'New P Rating' = "P4"
THEN FORMATDATE(DATE_SUB('Date Contract will Expire', INTERVAL 2 MONTH))
WHEN 'New P Rating' = "P3"
THEN FORMATDATE(DATE_SUB('Date Contract will Expire', INTERVAL 4 MONTH))
WHEN 'New P Rating' = "P2"
THEN FORMATDATE(DATE_SUB('Date Contract will Expire', INTERVAL 8 MONTH))
END
AS 'New Work Start Date'
FROM
(SELECT *,
CASE
WHEN ('Contract Value') <= 500000 THEN "P4"
WHEN ('Contract Value') BETWEEN 500001 AND 5000000 THEN "P3"
WHEN ('Contract Value') BETWEEN 5000001 AND 20000000 THEN "P2"
WHEN ('Contract Value') >=20000000 THEN "P1"
END AS 'New P Rating'
FROM T*)

[Stiltsoft support]

And another option from us regarding the CASE WHEN statement: you may also use the IF function as well. Then the query may look shorter.

For example, for your case where 

P4 = 2 months
P3 = 3 months
P2 = 5 months
P1 = 6 months

the query will be:

SELECT 'New P Rating', 'Date Contract will Expire',
FORMATDATE(DATE_SUB('Date Contract will Expire', INTERVAL
(IF('New P Rating' = "P4", 2,
(IF('New P Rating' = "P3", 3,
(IF('New P Rating' = "P2", 5, 6)))))) MONTH))
AS 'New Work Start Date'
FROM
(SELECT *,
CASE
WHEN ('Contract Value') <= 500000 THEN "P4"
WHEN ('Contract Value') BETWEEN 500001 AND 5000000 THEN "P3"
WHEN ('Contract Value') BETWEEN 5000001 AND 20000000 THEN "P2"
WHEN ('Contract Value') >=20000000 THEN "P1"
END AS 'New P Rating'
FROM T*)

[MudCo]

Thanks so much! It worked perfectly.  I was not able to use select within another select as it gave me an error but when I separated them into 2 transformers it worked just fine. Appreciate your help very much!!

[Stiltsoft support]

The issue with the SELECT inside another SELECT may be in referring to your columns.

In my example I call all my columns without the T1. prefixes. But it will also work fine if I use them in the internal SELECT:

(SELECT *,
CASE
WHEN (T1.'Contract Value') <= 500000 THEN "P4"
WHEN (T1.'Contract Value') BETWEEN 500001 AND 5000000 THEN "P3"
WHEN ('Contract Value') BETWEEN 5000001 AND 20000000 THEN "P2"
WHEN ('Contract Value') >=20000000 THEN "P1"
END AS 'New P Rating'
FROM T1)

And for the external SELECT (the first 7 rows of my example) you can't use the T1. prefix: you refer not to the original table that is T1 but to the already transformed table.

So, you may check this moment once more.

When you use two Table Transformer macros, there is no such issue as the table coming from the internal macro becomes the new original table for the external macro. So, you may or may not use T1. prefixes in both queries - do as you prefer.