Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
karan_kn
Creator II
Creator II

how to merge two flags

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;

1 Solution

Accepted Solutions
Digvijay_Singh

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

View solution in original post

10 Replies
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
MVP
MVP

Hi Digvijay,

will alias filed work for concat?

karan_kn
Creator II
Creator II
Author

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
Specialist II
Specialist II

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

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
Master III
Master III

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

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

karan_kn
Creator II
Creator II
Author

null fields to replace with DateFlag

PID.JPG

Digvijay_Singh

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