COMPARE FIELDS - Hard question

[#1]

Hi my friend, how are you?

I have a HARD question, i try solve this a lot hours... i work more than 8h per day. (sorry my english, I'm brazilian ^^).

I try: LEFT JOIN, CONCATENATE, NULLASVALUE, KEEP, ETC.... but anything solve this.
The problem is:

CALENDAR

DATE

01/01/2010

02/01/2010

03/01/2010

05/01/2010

06/01/2010

PEOPLE HOUR/WORK

DATE	PEOPLE_NAME	HOUR/WORK
01/01/2010	JOSE	1
02/01/2010	JOSE	2
06/01/2010	JOSE	1
01/01/2010	MARIA	2
02/01/2010	MARIA	2
03/01/2010	MARIA	2
04/01/2010	LUANA	1
04/01/2010	LUANA	5
05/01/2010	LUANA	1

FINAL - all days of Calendar, if PEOPLE not work in DAY (of calendar), HOUR/WORK = 0

DATE	PEOPLE_NAME	HOUR/WORK
01/01/2010	JOSE	1
02/01/2010	JOSE	2
03/01/2010	JOSE	0
04/01/2010	JOSE	0
05/01/2010	JOSE	0
06/01/2010	JOSE	1
01/01/2010	MARIA	2
02/01/2010	MARIA	2
03/01/2010	MARIA	2
04/01/2010	MARIA	0
05/01/2010	MARIA	0
06/01/2010	MARIA	0
01/01/2010	LUANA	0
02/01/2010	LUANA	1
03/01/2010	LUANA	0
04/01/2010	LUANA	5
05/01/2010	LUANA	1
06/01/2010	LUANA	0

Example 2:


This case: I need see ALL DAYS = 01/11/2009 ~ 30/11/2009, and applied a rule: IF people not work in day, HOUR/WORK = 0.


I know, this question its sooo hard, and sorry for question to ur 😄

But I cant solve this --"





Att, Patrick Maciel

[#2 · Accepted solution]

On your chart, have you tried to Show All Values on the Dimensions and turned off Suppress Zero-Values? That should get you all dates for every person using only the PEOPLE HOUR/WORK table. I've attached a sample (left chart).

My other thought was using a Cartesian Product on the Dates and an Employee list. Then that should join to your Work table giving you what you want (right chart).

WorkHours:
LOAD * INLINE [
DATE,PEOPLE_NAME,WORK
01/01/2010,JOSE,1
02/01/2010,JOSE,2
06/01/2010,JOSE,1
01/01/2010,MARIA,2
02/01/2010,MARIA,2
03/01/2010,MARIA,2
04/01/2010,LUANA,1
04/01/2010,LUANA,5
05/01/2010,LUANA,1
];

People:
LOAD * INLINE [
PEOPLE_TEMP
JOSE
MARIA
LUNA
];

Dates:
LOAD * INLINE [
DATE_TEMP
01/01/2010
02/01/2010
03/01/2010
05/01/2010
06/01/2010
];

AllDates:
LOAD DATE_TEMP As DATE RESIDENT Dates;
OUTER JOIN LOAD PEOPLE_TEMP AS PEOPLE_NAME RESIDENT People;
DROP TABLE People;
DROP TABLE Dates;

My QlikView Load Script syntax is not the best, so there may be more effecient ways to handle it.

[#3]

Hi Patrick,

here are a another (not perfect) example.

Hope it helps!

Rainer

[#4]

Thanks NMiller and Rainer Filoda.

I will test ur solution now.

W8 a moment 🙂

Thanks soooooooooo much 😉

[#5]

NMiller and Rainer Filoda.

I want to thanks again, because ur solve my problem.

I test your script NMiller and its perfect.

Filoda, I test you too, and ur script logic its perfect too...

Now I need decided... what? kkkkkk

But it is not important, what matters is that you have helped me.
And with your help, I managed to solve my problem.

Thank you very much.