16 Replies Latest reply: Jul 14, 2017 7:03 PM by Marco Wedel RSS

    creating a new column using existing column

    Syed Quadri

      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.

        • Re: creating a new column using existing column
          Krishnapriya Arumugam

          HI,

           

          Can you please share some sample data.

          • Re: creating a new column using existing column
            Vishwarath Nagaraju

            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, 

            • Re: creating a new column using existing column
              Wallo Atkinson

              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

              • Re: creating a new column using existing column
                Wallo Atkinson

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

                • Re: creating a new column using existing column
                  Mary Jo Taft

                  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
                      Syed Quadri

                      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.

                      • Re: creating a new column using existing column
                        Syed Quadri

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

                          • Re: creating a new column using existing column
                            Mary Jo Taft

                            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,

                              • Re: creating a new column using existing column
                                Mary Jo Taft

                                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,

                          • Re: creating a new column using existing column
                            Marco Wedel

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

                             

                            thanks

                             

                            regards

                             

                            Marco

                              • Re: creating a new column using existing column
                                Syed Quadri

                                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.

                                  • Re: creating a new column using existing column
                                    Vishwarath Nagaraju

                                    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$);

                                    • Re: creating a new column using existing column
                                      Wallo Atkinson

                                      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

                                      [C:\Users\smquadri\Desktop\Sample_Data.xlsx]

                                      (ooxml, embedded labels, table is Sheet1);

                                        • Re: creating a new column using existing column
                                          Mary Jo Taft

                                          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

                                            • Re: creating a new column using existing column
                                              Wallo Atkinson

                                              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
                                                  Marco Wedel

                                                  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 [D:\Daten\QlikView\QlikCommunity\Sample_Data.xls] (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