Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

creating a new column using existing column

Hello,

I am having little bit of a difficulty trying to create a new column using if statement on an existing column

i am doing

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

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

     IF(NRTT_Plan='TERM',3,

     IF(NRTT_Plan='TERM' AND Member_Age='0.26', 4))) as Status_Flag,  

but this does not work. i tired putting them in parenthesis but only  IF(NRTT_Plan='TERM',3, is loaded into table and nothing else.


What am i doing wrong here. Please help me.



Thank you,

Syed.

16 Replies
Partner
Partner

Re: creating a new column using existing column

HI,

Can you please share some sample data.

vishsaggi
Esteemed Contributor III

Re: creating a new column using existing column

Try this may be:

  IF(Match(NRTT_Plan, 'NEW,'RENEW','TRANSFER IN'), 1,

  IF(Match(NRTT_Plan, 'TERM'), 2,

  IF(Match(NRTT_Plan, 'TERM') AND Member_Age='0.26', 4))) as Status_Flag, 

atkinsow
Valued Contributor II

Re: creating a new column using existing column

Try

If(match(NRTT_Plan,'RENEW',1,

     if(match(NRTT_Plan,'TRANSFER IN'),2,

     if(match(NRTT_Plan,'TERM') AND Member_Age='0.26',4,3))) as Status_Flg

Highlighted
atkinsow
Valued Contributor II

Re: creating a new column using existing column

If you don't move the TERM and Member Age logic above the regular TERM logic you will never get any number 4 statuses.

Partner
Partner

Re: creating a new column using existing column

Syed,

Are you trying to put these into one and only one slot 1 - 4?  Because you have duplication.

In NRTT_Plan can there be only one of the values or multiple.

You could do it either of these ways, however you will get the same records meeting conditions 1 & 2

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

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

     IF(NRTT_Plan='TERM' AND Member_Age='0.26', 4,

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

IF(Match(NRTT_Plan,'NEW,'RENEW','TRANSFER IN')>0, 1,

     IF(Match(NRTT_Plan,'NEW,'TRANSFER IN')>0, 2,

     IF(NRTT_Plan='TERM' AND Member_Age='0.26', 4,

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

The only difference between your first condition and second seems to be that #1 has 'RENEW' in it

Without knowing your intention - it is hard to give you direction.  Its possible that you want the same record to appear with a value 1 and 2.  Only you know that answer.

You did need another right paren on your IF statement -- you had 4 conditions so you needed 4 right parens ')' at the end.

Re: creating a new column using existing column

please post some sample values and your expected result as well.

thanks

regards

Marco

Not applicable

Re: creating a new column using existing column

this four flags are for

1= active

2=new

3=Termed

4= termed at the age of 26

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 Tranfer in as renwed members are already established and cannot be considered new.

3 is termed  and 4 is Termed at age 26.

so yes, #2 will be same as #1 - Renewed members

i followed the steps that Wallo Atkinson suggested but the new field is not generated in the resulting column. Will try your method and see if it works.

Thank you,

Syed.

Not applicable

Re: creating a new column using existing column

i tried your  both approaches but non were able to load filed 'New' for me. 

Not applicable

Re: creating a new column using existing column

please find the sample data in the attachment. I need four groups but no matter how i try it, the resultant column has only 3. I am not able to load field 'New' in it.