Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can someone help me, how to merge two flags (DateFlag and Flag fields) refer highlighted red text.
Script:
T1:
LOAD *,
IF(MonthDiff >= 1 AND MonthDiff <= 12, 'Flag1',
IF(MonthDiff >=13 AND MonthDiff <= 36, 'Flag2',
IF(MonthDiff >=37 AND MonthDiff <= 60, 'Flag3','New'))) AS DateFlag
LOAD *,
IF(ISNULL(Max_Sales_Date) = -1, 'Null', ((year(Sales_Date)*12)+month(Sales_Date)) - (((year(Max_Sales_Date)*12)+ month(Max_Sales_Date)))) AS MonthDiff
Resident Temp;
DROP Table Temp;
T2:
LOAD
C_ID,
P_ID,
Sales_Date,
Max_Sales_Date,
MonthDiff,
DateFlag,
If(WildMatch(DateFlag,'New'),
If(C_ID = Previous(C_ID), 'Flag1', 'New')) as Flag
Resident T1 Order by C_ID, Sales_Date, P_ID;
May be this -
T2:
Load *,
if(DateFlag=Flag,DateFlag,Flag) as MergedFlag;
LOAD
C_ID,
P_ID,
Sales_Date,
Max_Sales_Date,
MonthDiff,
DateFlag,
If(WildMatch(DateFlag,'New'),
If(C_ID = Previous(C_ID), 'Flag1', 'New')) as Flag
Resident T1 Order by C_ID, Sales_Date, P_ID;
//Drop individual fields if you want to
Drop Fields DateFlag,Flag
May be try like this -
T2:
Load *,
DateFlag &'-'&Flag as MergedFlag;
LOAD
C_ID,
P_ID,
Sales_Date,
Max_Sales_Date,
MonthDiff,
DateFlag,
If(WildMatch(DateFlag,'New'),
If(C_ID = Previous(C_ID), 'Flag1', 'New')) as Flag
Resident T1 Order by C_ID, Sales_Date, P_ID;
//Drop individual fields if you want to
Drop Fields DateFlag,Flag
Hi Digvijay,
will alias filed work for concat?
Thanks for the response, but the result coming like below
Flag1- |
Flag2- |
Flag3- |
New-Flag1 |
New-New |
Expected Result :
Flag1
Flag2
Flag3
New
Use this
LOAD
C_ID,
P_ID,
Sales_Date,
Max_Sales_Date,
MonthDiff,
DateFlag as mergeflag,
If(WildMatch(DateFlag,'New'),
Resident T1 Order by C_ID, Sales_Date, P_ID;
Concatenate
LOAD
C_ID,
P_ID,
Sales_Date,
Max_Sales_Date,
MonthDiff,
If(WildMatch(DateFlag,'New'),
If(C_ID = Previous(C_ID), 'Flag1', 'New')) as mergeflag
Resident T1 Order by C_ID, Sales_Date, P_ID;
DROP TABLE T1;
Now 2 fields are merged into one.
Yes but only in preceeding load as I used.
May be this -
T2:
Load *,
if(DateFlag=Flag,DateFlag,Flag) as MergedFlag;
LOAD
C_ID,
P_ID,
Sales_Date,
Max_Sales_Date,
MonthDiff,
DateFlag,
If(WildMatch(DateFlag,'New'),
If(C_ID = Previous(C_ID), 'Flag1', 'New')) as Flag
Resident T1 Order by C_ID, Sales_Date, P_ID;
//Drop individual fields if you want to
Drop Fields DateFlag,Flag
if (len (trim (DateFlag))>0,DateFlag,
If(C_ID = Previous(C_ID), 'Flag1', 'New'))) as Flag
null fields to replace with DateFlag
If(len(Flag)=0,DateFlag,Flag) as Flag