Issue may be in sql query or may be data table structure : Suggest me the solution

[rahulhv1]

Pls. suggest me the solution, i think its not possible in QlikView.

1. Employee Master Table (Number of employees more than 2000)

(Sample size of data is as under ).

Employee Master Table

empcode	name
1	rahul
2	abhijeet
3	ranjeet
4	sachin
5	anil
6	vinod
7	manish
8	abcd

2. Attendance record file:  Pls. note tha more than 2000 records available and entiries are for only those employees who are present on the date are available. Employees which are not present is not available in this table,

(Sample size of data is as under )

Daily Attendence Table

empcode	name	date
1	rahul	02-Aug-14
2	abhijeet	02-Aug-14
3	ranjeet	02-Aug-14
4	sachin	02-Aug-14
5	anil	02-Aug-14

3. Result i want :  Result i want is that, whenever user selects  date from list box (in this example 02 Aug 2014) , result should be like as under

empcode	name	date	status
1	rahul	02-Aug-14	Present
2	abhijeet	02-Aug-14	Present
3	ranjeet	02-Aug-14	Present
4	sachin	02-Aug-14	Present
5	anil	02-Aug-14	Present
6	vinod	02-Aug-14	Absent
7	manish	02-Aug-14	Absent
8	abcd	02-Aug-14	Absent

[VishalWaghole]

Hi Rahul,

PFA qvw application, Hope it will help for you.

-- Regards,

Vishal Waghole

[VishalWaghole]

Hi Rahul,

Go to edit script and use this logic,

Emp:

LOAD * Inline [

empcode,    name

1,    rahul

2,    abhijeet

3,    ranjeet

4,    sachin

5,    anil

6,    vinod

7,    manish

8,    abcd

];

Atte:

LOAD * Inline [

empcode,    name,    date

1,    rahul,    02-Aug-14

2,    abhijeet,    02-Aug-14

3,    ranjeet,    02-Aug-14

4,    sachin,    02-Aug-14

5,    anil,    02-Aug-14

];

Left Join(Emp)

LOAD    empcode,

        name as empname,

        date

Resident    Atte;

drop Table Atte;

Empf:

LOAD     *,

        if(IsNull(date),'Absent','Present') as AtteFlag

Resident Emp;

DROP Table Emp;   

-- Regards,

Vishal Waghole

[rahulhv1]

Hi vishal, thankx for your reply, but as i mentioned in the first line that i have more than 2000 employees, and hence can not create table with function INLINE. and morever, new employees joins and even few leaves the organization, and hence can not user inline function as the data is not static

[its_anandrjs]

The one solution from that you can do your query see the below load query and do load like that

Ex:-

TmpT1:

LOAD * INLINE [

    empcode, name

    1, rahul

    2, abhijeet

    3, ranjeet

    4, sachin

    5, anil

    6, vinod

    7, manish

    8, abcd

];

Join

LOAD * INLINE [

    empcode, name, date

    1, rahul, 02-Aug-14

    2, abhijeet, 02-Aug-14

    3, ranjeet, 02-Aug-14

    4, sachin, 02-Aug-14

    5, anil, 02-Aug-14

];

Final:

LOAD

empcode, name, date,

if(Len(date)> 0, 'Present','Absent') as Status

Resident TmpT1;

DROP Table TmpT1;

[its_anandrjs]

In place of the inline load use your table name and load statement see the example

TmpT1:

LOAD

empcode, name

From Master;

Join

LOAD

empcode, name, date

From AttendanceDetails

Final:

LOAD

empcode, name, date,

if(Len(date)> 0, 'Present','Absent') as Status

Resident TmpT1;

DROP Table TmpT1;

[VishalWaghole]

Hi Rehul,

even though your data is coming from database, u can use

if(IsNull(date),'Absent','Present') as AtteFlag

in your script.

Like

Load    empcode,

           name,

           date,

           if(IsNull(date),'Absent','Present') as AtteFlag

From   yoursdatasourcename;

Hope you understand.

-- Regards,

Vishal Waghole

[rahulhv1]

This i have tried, but when end user selects any date from the list box , it shows only records where date is not null , i.e. it shows only records  for the employees who are present and not showing the list of employees who are absent on the perticular date.

[VishalWaghole]

Hi Rahul,

Exactly right, because of QlikView associativity.

If you select any date, then your dashboard will reflect with respective data.

If you want to does not reflect with data dimension then you can write set analysis.

-- Regards,

Vishal Waghole

[preminqlik]

Temp:

Load EmployeeCode&'~'&EmployeeName               as               EmpKey

from master;

join

Load distinct  Date

from Atte;

FINAL:

Load subfield(EmpKey,'~',1)               as               EmployeeCode,

subfield(EmpKey,'~',2)                         as               EmployeeName,

Date

resident Temp;

left join

Load EmployeeCode,

EmployeeName,

Date

'Attendence'               as               Tab_flag

resident Atte;

drop table Temp;

Final_FINAL:

Load *,

if(Tab_flag='Attendence','Present','Absent')                    as     Status

resident FINAL_STAGE1;

drop table FINAL_STAGE1;