2 Replies Latest reply: Feb 12, 2018 5:54 AM by Juraj Misina RSS

    Compare selections from one field value with another field value

    kiran Raj

      Hi All,

       

      I am building an app to check the data before loading into  qlik layer.

       

      The scenario is to find the fields which is associated with multiple values in other table. (i.e) 1 employee should be associated only to 1 manager in the same month.

      If 1 employee is associated to different managers in the same month then it is data error.

       

      Manager initialManager NameEmployee InitialEmployee NameMonth
      JPJohnSASamsungJan - 18
      KSKrishNSNathan SamFeb - 18
      PLPrashanthKRKarthikFeb - 18
      RNRizwanKRKarthikFeb - 18
      kSKishoreLALaxmanFeb - 18

       

       

       

      In the given example excel if 1 "employee initial & Employee Name" combination is associated to 2 different "manager initial & manager Name"  combination in the same month, then it is error in data.

       

      Here, Employee 'Karthik' is linked to 2 different managers 'Prashanth' and 'Rizwan' in the same month. which needs to throw error in the application which I am building.

       

      Could anyone help me out in this?

        • Re: Compare selections from one field value with another field value
          omar bensalem

          Maybe:

          table:

          load *, "Employee Name"&'|'&Month as Key Inline [

           

          Manager initial, Manager Name, Employee Initial, Employee Name, Month

          JP, John, SA, Samsung, Jan - 18

          KS, Krish, NS, Nathan Sam, Feb - 18

          PL, Prashanth, KR, Karthik, Feb - 18

          RN, Rizwan, KR, Karthik, Feb - 18

          kS, Kishore, LA, Laxman, Feb - 18

          ];

           

          NoConcatenate

          Final:

          load Key, if(CountManager=1,SubField(Key,'|',1),'Error for Employee '&SubField(Key,'|',1)) as "Employee Name",SubField(Key,'|',2) as Month ;

          load  Key, count( DISTINCT "Manager initial") as CountManager Resident table

          group by Key

          ;

           

          Left join(Final)

          load Key, "Manager initial", "Manager Name", "Employee Initial" Resident table;

          Drop Table table;

          drop field Key from Final;



          Result:

          Capture.PNG

           

          If u just want to import the Employees that have one manager per Month:

           

          table:

          load *, "Employee Name"&'|'&Month as Key Inline [

           

          Manager initial, Manager Name, Employee Initial, Employee Name, Month

          JP, John, SA, Samsung, Jan - 18

          KS, Krish, NS, Nathan Sam, Feb - 18

          PL, Prashanth, KR, Karthik, Feb - 18

          RN, Rizwan, KR, Karthik, Feb - 18

          kS, Kishore, LA, Laxman, Feb - 18

          ];

           

           

          NoConcatenate

          Final:

          load Key, SubField(Key,'|',1) as "Employee Name",SubField(Key,'|',2) as Month  where CountManager=1;

          load  Key, count( DISTINCT "Manager initial") as CountManager Resident table

          group by Key

          ;

           

          Left join(Final)

          load Key, "Manager initial", "Manager Name", "Employee Initial" Resident table;

          Drop Table table;

           

          drop field Key from Final;


          Result:

          Capture.PNG

          • Re: Compare selections from one field value with another field value
            Juraj Misina

            Hi Kiran,

             

            you can do so in script:

            LOAD
                 [Employee Initial],
                 [Employee Name],
                 Month,
                 If(Count([Manager Initial]&[Manager Name])>1, 'Error', 'OK') as ErrorCheck
            From Source
            Group By
                 [Employee Initial],
                 [Employee Name],
                 Month
            ;
            

            And analogically for other required checks.

             

            Hope this helps.

            Juraj