Conditional Load

[#1]

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

[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]);

[jagan]

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

[manojkulkarni]

You have 2 options

1. using left join

2. Exists function

refer this link

Load From one table by values from another

[#5]

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 ' []'

[#6]

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

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

[Kushal_Chawda]

Hi senthil.psk1‌ 

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