Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

karan_kn
New Contributor III

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
Honored Contributor III

Re: how to merge two flags

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

10 Replies
Digvijay_Singh
Honored Contributor III

Re: how to merge two flags

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

sureshqv
Esteemed Contributor III

Re: how to merge two flags

Hi Digvijay,

will alias filed work for concat?

karan_kn
New Contributor III

Re: how to merge two flags

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
Valued Contributor

Re: how to merge two flags

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
Honored Contributor III

Re: how to merge two flags

Yes but only in preceeding load as I used.

Digvijay_Singh
Honored Contributor III

Re: how to merge two flags

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
Honored Contributor III

Re: how to merge two flags

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

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

karan_kn
New Contributor III

Re: how to merge two flags

null fields to replace with DateFlag

PID.JPG

Digvijay_Singh
Honored Contributor III

Re: how to merge two flags

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

Community Browser