how to merge two flags

[karan_kn]

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;

[Digvijay_Singh]

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

[Chanty4u]

Hi Digvijay,

will alias filed work for concat?

[karan_kn]

Thanks for the response, but the result coming like below

Flag1-

Flag2-

Flag3-

New-Flag1

New-New

Expected Result :

Flag1

Flag2

Flag3

New

[qv_testing]

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.

[Digvijay_Singh]

Yes but only in preceeding load as I used.

[Digvijay_Singh · Accepted solution]

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

[sasiparupudi1]

if (len (trim (DateFlag))>0,DateFlag,

If(C_ID = Previous(C_ID), 'Flag1', 'New'))) as Flag

[karan_kn]

null fields to replace with DateFlag

[Digvijay_Singh]

If(len(Flag)=0,DateFlag,Flag) as Flag