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

[rahulhv1]

sorry prem, its not working

[VishalWaghole]

Hi Rahul,

Please upload some sample Qvw with dummy data.

i will try and let you know.

-- Regards,

Vishal Waghole

[rahulhv1]

How to send attachment? *.XLS & QVW

[preminqlik]

go to "Use advance editor" in right top side in comment section after pressing reply button

[rahulhv1]

Thanks for your help prem, I tried it and i solved the issue. Below is the code that executed and it worked as per my requirment.

table1:

LOAD empcode,
name,dept
FROM

(ooxml, embedded labels, table is Sheet1);
join
LOAD DISTINCT date
FROM

(ooxml, embedded labels, table is Sheet1);

left join
load empcode,name, date as realdate from

(ooxml, embedded labels, table is Sheet1);

table2:
load empcode ,name,date,dept,realdate, if(date=realdate ,'Present','Absent') as status Resident table1;

load MaxString(status) as stauts, empcode ,name,date,dept resident table2 group by empcode, name,date,dept;
drop table table1;
tab3:
load status,date,realdate, empcode ,name,dept resident table2;
drop table table2;

[rahulhv1]

Hi Vishal, Thanks for your help, i have done it with below code

table1:

LOAD empcode,
name,dept
FROM

(ooxml, embedded labels, table is Sheet1);
join
LOAD DISTINCT date
FROM

(ooxml, embedded labels, table is Sheet1);

left join
load empcode,name, date as realdate from

(ooxml, embedded labels, table is Sheet1);

table2:
load empcode ,name,date,dept,realdate, if(date=realdate ,'Present','Absent') as status Resident table1;

load MaxString(status) as stauts, empcode ,name,date,dept resident table2 group by empcode, name,date,dept;
drop table table1;
tab3:
load status,date,realdate, empcode ,name,dept resident table2;
drop table table2;

[preminqlik]

HI,

ok .for your crystal clear understanding ..in the first step left joined with distinct date is known as Cartesian Product.

[rahulhv1]

Thanks a lot for the same

[VishalWaghole]

Thats gr8 job Rahul.... keep it up..!!!