Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

7 Replies
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
Partner - Champion III
Partner - Champion III

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
Partner - Specialist II
Partner - Specialist II

You have 2 options

1. using left join

2. Exists function

refer this link

Load From one table by values from another

Not applicable
Author

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

Not applicable
Author

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