topic Re: how to merge two flags in QlikView

how to merge two flags

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: how to merge two flags

Digvijay_Singh — Tue, 13 Mar 2018 19:07:07 GMT

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

Re: how to merge two flags

Chanty4u — Tue, 13 Mar 2018 19:12:23 GMT

Hi Digvijay,

will alias filed work for concat?

Re: how to merge two flags

karan_kn — Tue, 13 Mar 2018 19:17:52 GMT

Thanks for the response, but the result coming like below

Flag1-

Flag2-

Flag3-

New-Flag1

New-New

Expected Result :

Flag1

Flag2

Flag3

New

Re: how to merge two flags

Nagaraju_KCS — Tue, 13 Mar 2018 19:19:07 GMT

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.

Re: how to merge two flags

Digvijay_Singh — Tue, 13 Mar 2018 19:25:39 GMT

Yes but only in preceeding load as I used.

Re: how to merge two flags

Digvijay_Singh — Tue, 13 Mar 2018 19:32:10 GMT

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

Re: how to merge two flags

sasiparupudi1 — Tue, 13 Mar 2018 19:45:16 GMT

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

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

Re: how to merge two flags

karan_kn — Tue, 13 Mar 2018 19:46:42 GMT

null fields to replace with DateFlag

Re: how to merge two flags

Digvijay_Singh — Tue, 13 Mar 2018 19:58:20 GMT

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

Re: how to merge two flags

karan_kn — Wed, 14 Mar 2018 04:16:31 GMT

Perfect, Thanks so much,