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

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

rahulhv1 — Sat, 19 Jul 2014 04:41:57 GMT

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

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

VishalWaghole — Sat, 19 Jul 2014 05:13:15 GMT

Hi Rahul,

PFA qvw application, Hope it will help for you.

-- Regards,

Vishal Waghole

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

VishalWaghole — Sat, 19 Jul 2014 05:14:34 GMT

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

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

rahulhv1 — Sat, 19 Jul 2014 05:19:07 GMT

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

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

its_anandrjs — Sat, 19 Jul 2014 05:19:38 GMT

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;

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

its_anandrjs — Sat, 19 Jul 2014 05:22:10 GMT

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;

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

VishalWaghole — Sat, 19 Jul 2014 05:34:42 GMT

Hi Rehul,

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

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

in your script.

Load empcode,

name,

date,

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

From yoursdatasourcename;

Hope you understand.

-- Regards,

Vishal Waghole

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

rahulhv1 — Sat, 19 Jul 2014 05:37:02 GMT

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.

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

VishalWaghole — Sat, 19 Jul 2014 05:44:31 GMT

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

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

preminqlik — Sat, 19 Jul 2014 08:16:51 GMT

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;

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

rahulhv1 — Sat, 19 Jul 2014 09:48:24 GMT

sorry prem, its not working

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

VishalWaghole — Sat, 19 Jul 2014 10:01:27 GMT

Hi Rahul,

Please upload some sample Qvw with dummy data.

i will try and let you know.

-- Regards,

Vishal Waghole

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

rahulhv1 — Sat, 19 Jul 2014 10:43:08 GMT

How to send attachment? *.XLS & QVW

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

preminqlik — Sat, 19 Jul 2014 16:22:21 GMT

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

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

rahulhv1 — Mon, 21 Jul 2014 08:02:02 GMT

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;

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

rahulhv1 — Mon, 21 Jul 2014 08:02:39 GMT

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

table1:

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

preminqlik — Mon, 21 Jul 2014 10:21:13 GMT

HI,

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

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

rahulhv1 — Mon, 21 Jul 2014 11:19:35 GMT

Thanks a lot for the same

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

VishalWaghole — Mon, 21 Jul 2014 13:12:37 GMT

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