Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vishsaggi
Esteemed Contributor III

Re: creating a new column using existing column

Try this? Change the path accordingly.

Table1:

LOAD Age,

     [Member #],

     NRTT_Plan,

     Payment,

     IF(NRTT_Plan='NEW' or NRTT_Plan ='TRANSFER IN',2,

     IF(NRTT_Plan='NEW' or NRTT_Plan ='RENEW' or NRTT_Plan ='TRANSFER IN', 1,

     IF(NRTT_Plan='TERM' AND Age='26', 4,

     IF(NRTT_Plan='TERM',3 )))) as Status_Flag

FROM

[..\Desktop\RoughForum_Files\Sample_Data.xls]

(biff, embedded labels, table is Sheet1$);

atkinsow
Valued Contributor II

Re: creating a new column using existing column

That's because everything that WOULD be in Status 2 falls into status 1 first.

You have some redundancy.  You can't have a record be status 1 and 2 simultaneously.

Which would take precedence active or new?  Can a record be New but not Active?

Wild guess, but maybe you really want.

LOAD NRTT_Plan,

     IF(NRTT_Plan='NEW' or NRTT_Plan ='TRANSFER IN',2,

     IF( NRTT_Plan ='RENEW' , 1,

     IF(NRTT_Plan='TERM' AND Age='26', 4,

     IF(NRTT_Plan='TERM',3 )))) as Status_Flag,

     [Member #],

     Payment,

     Age

FROM

(ooxml, embedded labels, table is Sheet1);

Partner
Partner

Re: creating a new column using existing column

If using Match - that is case sensitive so your case has to match also.  'NEW' is not the same as 'New' or 'new'.  If you want case insensitive then use Mixmatch.  Let me check my answer now that you have posted more information.

1 which is "Active' can be New members, renewed members and Transfer in

2 which is 'New' can be members that just signed up and are new to the plan. so they can only be New or Transfer in as renewed members are already established and cannot be considered new.

Ok so you will need an additional condition for #2 which says they cannot be 'RENEW'

IF((NRTT_Plan<>'RENEW' AND (NRTT_Plan= 'NEW' or NRTT_Plan ='TRANSFER IN')),2,

Partner
Partner

Re: creating a new column using existing column

according to your sample - a member can only have one status flag.

You have to determine which takes precedence -- do you want to assign ACTIVE or NEW for those that meet more than one

You can try my reply from above and put that before the line you currently have for the ACTIVE members.  That would slot your RENEWS as #1 .... like this

IF((NRTT_Plan<>'RENEW' and (NRTT_Plan= 'NEW' or NRTT_Plan ='TRANSFER IN')),2,

  IF(NRTT_Plan='NEW' or NRTT_Plan ='RENEW' or NRTT_Plan ='TRANSFER IN', 1,

Partner
Partner

Re: creating a new column using existing column

I agree with your solution if a person can only have 1 plan.  If a single member can have multiple plans - then I think the premise needs to be reworked.  According to the sample data - it would appear a member has only 1 plan, but perhaps the sample does not tell the whole story

atkinsow
Valued Contributor II

Re: creating a new column using existing column

Agreed.

If a person needs to be counted in more than 1 then it probably should be done in Set Analysis on the frontend.

Otherwise the Status Flag should be put in a separate table in the data model with a 1 to many join in the data model.

So each record can have more than 1 status.

Re: creating a new column using existing column

Hi,

I guess your separate status_flag table is the best solution in this case.

One possible implementation could be:

QlikCommunity_Thread_267633_Pic1.JPG

QlikCommunity_Thread_267633_Pic2.JPG

table1:

LOAD AutoNumberHash128(NRTT_Plan,Age) as %StatusKey,

    NRTT_Plan,

    [Member #],

    Payment,

    Age

FROM (biff, embedded labels, table is Sheet1$);

tabStatus:

CrossTable (temp, Status_Flag)

LOAD Distinct

    %StatusKey,

    If(NRTT_Plan='NEW' or NRTT_Plan ='TRANSFER IN',2),

    If(NRTT_Plan='NEW' or NRTT_Plan ='RENEW' or NRTT_Plan ='TRANSFER IN', 1),

    If(NRTT_Plan='TERM' AND Age='26', 4),

    If(NRTT_Plan='TERM',3 )

Resident table1;

DROP Field temp;

hope this helps

regards

Marco