8 Replies Latest reply: Dec 9, 2013 4:53 AM by Fabrice Aunez RSS

    Join two IF function fields

    ralph snijckers

      Hi,

       

      In the Qlikview script I have two IF functions in the same table.

         

      KOB1:
      QUALIFY *;

      LOAD

      Kostensoort,
      IF(Kostensoort<6040000,'Materiaal',IF(Kostensoort<7000000,'Uitbesteding',IF(Kostensoort<9000000,'Netto gefactureerd aan derden',IF(Kostensoort<9999999,'Uren','Niet toegekend'))))as A,
      Leverancier,

      If(Match(Leverancier, 'Cofely Experts BV','Laudy Bouw & Planontwikkeling bv')>0,Leverancier, 'Leverancier contract') as LeverancierSamen

       

      I'd like to create a new field  'A' + 'LeverancierSamen'

      Example:

      Kostensoort; A ; LeverancierSamen; NewField

      6000001; Materiaal ; Supplier 15 ; Materiaal Supplier 15

      6040001; Uitbesteding ; Supplier 10 ; Uitbesteding Supplier

      6040021; Uitbesteding ; Supplier 33 ; Uitbesteding Supplier 33

       

      Grtz,

      Ralph

       

        • Re: Join two IF function fields
          sai v

          Hi Ralph,

           

          You can create a variable which could be Field1 & Field 2 that will append both of these

           

          Thanks,

          Sai

          • Re: Join two IF function fields

            Hi,

             

            I would use the technique of preceding load.

            TableA:

            LOAD A,

                   Leverencier,

                 A + Leverencier as B;

            Your LOAD statement FROM the source;

             

            There are 2 LOAD at the row. The really loaded is the first one (there is no FROM xxx). The second  LOAD "creates a temp table" from the source.

             

            Fabrice

              • Re: Join two IF function fields
                ralph snijckers

                Hello Aunez,

                 

                Will you help me with my "LOAD statement FROM the source".

                The script:

                 

                KOB1:
                QUALIFY *;

                LOAD Kostensoort,
                IF(Kostensoort<6040000,'Materiaal',IF(Kostensoort<7000000,'Uitbesteding',IF(Kostensoort<9000000,'Netto gefactureerd aan derden',IF(Kostensoort<9999999,'Uren','Niet toegekend'))))as A,
                Kostensoortomschr.,
                Inkoopdocument,
                Leverancier,
                If(Match(Leverancier, 'Cofely Experts BV','Laudy Bouw & Planontwikkeling bv')>0,
                Leverancier, 'Leverancier contract') as LeverancierSamen,
                Besteltekst,
                [Totl. ingev. hoevh.],
                [Geboekte hvh.eenh.],
                [Partner prest.soort],
                [Waarde/CO-valuta],
                Personeelsnummer,
                [Naam van werknemer of sollicitant],
                [Hoeveelheid totaal],
                Hoeveelheidseenheid,
                Documentdatum,
                Month(Documentdatum) as Maand,
                Week(Documentdatum) as Week,
                Objectomschrijving,
                EC_Order
                FROM
                Data\SAPDumpFinancieel.xlsx
                (
                ooxml, embedded labels, table is KOB1);
                UNQUALIFY *;

                 

                I tried:

                 

                 

                TableA:

                LOAD

                KOB1.A,
                KOB1.LeverancierSamen,
                KOB1.A + KOB1.LeverancierSamen as B
                FROM KOB1;

                 

                Thanks a lot,

                Ralph

                  • Re: Join two IF function fields

                    Ralph,

                     

                    To explain a little more about PRELOAD:

                     

                     

                    Qualify *;
                    Table1:
                    LOAD Prod,
                    Month,
                    Flag1 & '|' & Flag2 as Flag,
                    Units

                    if (Flag1 = 'A', Amount) as Amount;
                    LOAD Prod,
                    Month,
                    Country,
                    Amount,
                    Units,
                    if(Month < 3, 'A', if(Country > 2, 'B', 'C')) as Flag1,
                    if(Amount > 50, 'X', if(Units < 10, 'Y', 'Z')) as Flag2

                    FROM[101221.xlsx] (ooxml, embedded labels, table is Feuil1);

                     

                    Your table will contain the following fields: Prod, Month, Flag, Units and Amount. No country.

                    The LOAD statement that loads the table is the first one: it has no source (no FROM, no RESIDENT). The LOAD that reads the file is the second one (but it does not populate the table)

                    As you see, Flag1 and Flag2 can be reused in the first LOAD.

                    To use all the fields of the second load, you can use the start (*) :LOAD *, if(Flag1 = .....) as Flag

                    Fabrice

                • Re: Join two IF function fields
                  Ravikant Bellad

                  Hi,

                   

                  try this,

                   

                  KOB1:

                  QUALIFY *;

                  LOAD

                  Kostensoort,
                  IF(Kostensoort<6040000,'Materiaal',IF(Kostensoort<7000000,'Uitbesteding',IF(Kostensoort<9000000,'Netto gefactureerd aan derden',IF(Kostensoort<9999999,'Uren','Niet toegekend'))))as A,
                  Leverancier,

                  If(Match(Leverancier, 'Cofely Experts BV','Laudy Bouw & Planontwikkeling bv')>0,Leverancier, 'Leverancier contract') asLeverancierSamen

                   

                  KOB2:

                  LOAD

                  Kostensoort,

                  A,

                  LeverancierSamen,

                  A &'-'& LeverancierSamen as NewField,

                  Resident KOB1;


                  Drop Table KOB1;

                   

                   

                   

                  Hope it helps,

                   

                   

                  Regards,

                  Ravikant

                  • Re: Join two IF function fields

                    hi

                     

                    try thi hope this will help

                     

                    KOB1:
                    QUALIFY *;

                    LOAD

                    Kostensoort,
                    IF(Kostensoort<6040000,'Materiaal',IF(Kostensoort<7000000,'Uitbesteding',IF(Kostensoort<9000000,'Netto gefactureerd aan derden',IF(Kostensoort<9999999,'Uren','Niet toegekend'))))as A,
                    Leverancier,

                    If(Match(Leverancier, 'Cofely Experts BV','Laudy Bouw & Planontwikkeling bv')>0,Leverancier, 'Leverancier contract') as LeverancierSamen

                    From .......

                     

                    KOBLink:

                    Load

                    8,

                    A&LeverancierSamen as Newfield

                    Resident KOB1;

                    Drop table KOB1;