7 Replies Latest reply: Jul 24, 2015 10:20 AM by kushal chawda RSS

    Conditional Load

      Hi

       

      I have 2 Tables:

      1. TeamList

      2. Attendance

       

      TeamList:

      LOAD [Emp ID] as BankID,

           Name,

           DOJ

      FROM

      C:\Sanction\Data\QVD\TeamList.qvd

      (qvd);

       

      Attendance:

      LOAD

          "[Bank ID]",

           Month("[Date]") & '_' & Year("[Date]")&'_' & "[Bank ID]" as Atte_Key,

           "[Staff Name]" as [Staff Name],

           "[Log Type]" as  [Log Type],

           "[Start Time]" as [Start Time],

           "[End Time]" as [End Time], 

           "[Duration (Min)]" as [Duration (Min)]

            FROM

      C:\Sanction\Data\Attendance\Attendance.qvd

      (qvd) where exists(BankID, "[Bank ID]");

       

       

      I have to load the Attendance Table only for the BankID present in the TeamList Table.

       

      I have used the above script to implement it. But its not working.

       

      Need your inputs

        • Re: Conditional Load
          kushal chawda

          try,

           

          TeamList:

          LOAD [Emp ID] as BankID,

               Name,

               DOJ

          FROM

          C:\Sanction\Data\QVD\TeamList.qvd

          (qvd);

           

          Attendance:

          LOAD

              "[Bank ID]",

               Month("[Date]") & '_' & Year("[Date]")&'_' & "[Bank ID]" as Atte_Key,

               "[Staff Name]" as [Staff Name],

               "[Log Type]" as  [Log Type],

               "[Start Time]" as [Start Time],

               "[End Time]" as [End Time],

               "[Duration (Min)]" as [Duration (Min)]

                FROM

          C:\Sanction\Data\Attendance\Attendance.qvd

          (qvd) where exists(BankID, [Bank ID]);

            • Re: Conditional Load

              Hi

               

              Thanks for the Reply.

               

              If i use the where exists condition which you mentioned its throwing error as Bank ID not found.

               

              The reason why the Bank ID is mentioned as "[Bank ID]" , in the source table the field comes with the [] , so to load it i have used ' []'

                • Re: Conditional Load
                  kushal chawda

                  try,

                   

                  TeamList:

                  LOAD [Emp ID] as BankID,

                       Name,

                       DOJ

                  FROM

                  C:\Sanction\Data\QVD\TeamList.qvd

                  (qvd);

                   

                  left join (TeamList)

                  LOAD

                      "[Bank ID]"  as BankID,

                       Month("[Date]") & '_' & Year("[Date]")&'_' & "[Bank ID]" as Atte_Key,

                       "[Staff Name]" as [Staff Name],

                       "[Log Type]" as  [Log Type],

                       "[Start Time]" as [Start Time],

                       "[End Time]" as [End Time],

                       "[Duration (Min)]" as [Duration (Min)]

                        FROM

                  C:\Sanction\Data\Attendance\Attendance.qvd

                  (qvd) ;

              • Re: Conditional Load
                jagan mohan rao appala

                Hi,

                 

                Try using Where Exists() without Double quotes like below

                 

                TeamList:

                LOAD [Emp ID] as BankID,

                     Name,

                     DOJ

                FROM

                C:\Sanction\Data\QVD\TeamList.qvd

                (qvd);

                 

                Attendance:

                LOAD

                    "[Bank ID]",

                     Month("[Date]") & '_' & Year("[Date]")&'_' & "[Bank ID]" as Atte_Key,

                     "[Staff Name]" as [Staff Name],

                     "[Log Type]" as  [Log Type],

                     "[Start Time]" as [Start Time],

                     "[End Time]" as [End Time],

                     "[Duration (Min)]" as [Duration (Min)]

                      FROM

                C:\Sanction\Data\Attendance\Attendance.qvd

                (qvd) where exists(BankID, [Bank ID]);

                 

                OR try with inner join.

                 

                TeamList:

                LOAD [Emp ID] as BankID,

                     Name,

                     DOJ

                FROM

                C:\Sanction\Data\QVD\TeamList.qvd

                (qvd);

                 

                Attendance:

                LOAD

                    "[Bank ID]",

                     Month("[Date]") & '_' & Year("[Date]")&'_' & "[Bank ID]" as Atte_Key,

                     "[Staff Name]" as [Staff Name],

                     "[Log Type]" as  [Log Type],

                     "[Start Time]" as [Start Time],

                     "[End Time]" as [End Time],

                     "[Duration (Min)]" as [Duration (Min)]

                      FROM

                C:\Sanction\Data\Attendance\Attendance.qvd

                (qvd)

                 

                INNER JOIN(Attendance)

                LOAD [Emp ID] as [Bank ID]    

                FROM

                C:\Sanction\Data\QVD\TeamList.qvd

                (qvd);

                  • Re: Conditional Load

                    Hi

                     

                    Thanks for the Reply.

                     

                    If i use the where exists condition which you mentioned its throwing error as Bank ID not found.

                     

                    The reason why the Bank ID is mentioned as "[Bank ID]" , in the source table the field comes with the [] , so to load it i have used ' []'


                    Inner Join also not working

                  • Re: Conditional Load
                    Manoj Kulkarni

                    You have 2 options

                    1. using left join

                    2. Exists function

                     

                    refer this link

                    Load From one table by values from another

                    • Re: Conditional Load
                      kushal chawda

                      Hi senthil 

                      Please close the thread by marking correct answer if you got the solution