LIKE function in Talend

For example there is a column order_payment_method , in which values in other format and you want to change in proper format , for that we have made some business rules as below.

StringHandling.LEN(row1.Order_Payment_Method) >  0 ? 

CommonDb.like(StringHandling.DOWNCASE(row1.Order_Payment_Method),  "%ccavenue-credit_card%")?"CCAvenue_CC":

CommonDb.like(StringHandling.DOWNCASE(row1.Order_Payment_Method),"%credit_card%")?"CreditCard":

CommonDb.like(StringHandling.DOWNCASE(row1.Order_Payment_Method),"%ccavenue%")?"CCAvenue": “N/A”

Here orders of the business rules are very important because if you have noticed there are two categories “credit_card” and “ccavenue-credit_card” so if you interchange the order it will change “ccavenue-credit_card” to CreditCard so you should put “ccavenue-credit_card” first then for “credit_card” same for others.

LIKE is better option if you are not sure what else will be coming with value otherwise you can use .equals() option , in above case you should better use .equals because those categories are fixed.

Best example for using like option is color, like if item name contains black then Black, and if contain “dusk black” then “Dusk Black”

Need to add commonDb code

CommonDb.like(StringHandling.DOWNCASE(main.Item_Name),"%dusk black%")?"Dusk Black":

CommonDb.like(StringHandling.DOWNCASE(main.Item_Name),"%deep black%")?"Deep Black":

CommonDb.like(StringHandling.DOWNCASE(main.Item_Name),"%black%")?"Black"

Generate rows by month for the month between Start Date and End Dates in Redshift

If you run the first only SELECT part which is for the sample data set you will see below dataset.


Sample Data Set :-

item_id	start_date	end_date	monthly_revenue
242345	2/26/2016	7/26/2016	700

Desired Output by month :-

item_id	month	monthly_revenue
242345	2/26/2016	700
242345	3/26/2016	700
242345	4/26/2016	700
242345	5/26/2016	700
242345	6/26/2016	700
242345	7/26/2016	700

Redshift Query to get above data set :-



WITH SampleData AS (
    SELECT
       CAST(242345 AS INTEGER) AS Item_Id
       ,CAST('2016-02-26' AS DATE) AS Start_Date
       ,CAST('2016-07-26' AS DATE) AS End_Date
       ,CAST(700 AS INTEGER) AS Monthly_Revenue
)

,cteTally AS (
        SELECT 0 AS TallyNum
        UNION ALL
        SELECT 1
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3
        UNION ALL
        SELECT 4
        UNION ALL
        SELECT 5
        UNION ALL
        SELECT 6
        UNION ALL
        SELECT 7
        UNION ALL
        SELECT 8
        UNION ALL
        SELECT 9
        UNION ALL
        SELECT 10
        UNION ALL
        SELECT 11
    )
    SELECT
        Item_ID,
        DATEADD(MONTH,   c.TallyNum,   t.Start_date) AS "Month",
        Monthly_Revenue
    FROM  SampleData t  INNER JOIN cteTally c
    ON DATEDIFF(MONTH,   t.Start_Date,   t.End_Date) >= c.TallyNum ;