topic Re: Conditional Load in QlikView

Conditional Load

Thu, 23 Jul 2015 06:37:21 GMT

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 — Thu, 23 Jul 2015 06:39:37 GMT

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

jagan — Thu, 23 Jul 2015 06:44:09 GMT

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

manojkulkarni — Thu, 23 Jul 2015 06:51:41 GMT

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

Thu, 23 Jul 2015 07:00:29 GMT

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

Thu, 23 Jul 2015 09:43:47 GMT

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

Kushal_Chawda — Thu, 23 Jul 2015 09:49:12 GMT

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

Kushal_Chawda — Fri, 24 Jul 2015 14:20:19 GMT

Hi senthil.psk1‌

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