Select over two tables (not joined)

[#1]

Hello and a happy new year to everybody!

I´m pretty new to QlikView and currently playing around a bit.

I would like to do a select using two tables. Table 1 contains a timeline of factors:

TABLE_1 (2 columns, multiple rows)
1: DATE: e.g. 01/01/2013
2: DECIMAL: e.g. 3.4

Table two contains multipliers for each weekday:

TABLE_2 (7 columns)
col1: MO: e.g. 9
col2: TU: e.g. 11
col3: WE: e.g. 5
col4: TH: e.g. 2
col5: FR: e.g. 8
col6: SA: e.g. 7
col7: SU: e.g. 10

What I am trying to do is the following:

SELECT DATE, DECIMAL FROM TABLE_1 ORDER BY DATE;
<foreach row do>

SELECT WEEKDAY($(DATE)) AS MULTIPLIER FROM TABLE_2;
LET $RESULT = $DECIMAL * $(MULTIPLIER)

<loop row>

That is, simply choose the right multiplier for each day and perform the calculation. As the two tables are not joined - there´s no direct key relation - I don´t know how to do it. Moreover the name of the column in table_2 is given dynamically as weekday from the date in column_1.

Can someboedy please give me some help?

Thanks a lot!

[chematos]

NameTable:

load *,

left(weekday(DATE), 2) as DayWeek;

select DATE, DECIMAL FROM TABLE_1 ORDER BY DATE;

left join

Load DayWeek,

MULTIPLIER;

SELECT WEEKDAY($(DATE)) as DayWeek, decimalField as MULTIPLIER FROM TABLE_2;

Table:

NoConcatenate

Load *,

Decimal * MULTIPLIER as Result

resident NameTable;

drop table NameTable;

[#3]

Hello José,

Thank´s a lot for your reply. Unfortunately I couldn´t get it to work according to what I want to achieve. Maybe the following picture will help.

So, let´s say we have some agents working on a certain project on different days of the week a certain amount of hours. Every weekday is evaluated using an individual factor. That is: value = factor * hours for each day of the year. In order to evaluate the whole timeline, I need to loop over the whole year and do the calculation for each agent, depending on the weekday corresponding to the respective date.

The result would look like this:

Weekday	Date	Peter	Paul	Mary
So	01.01.2012	2,5 * 0,011	5 * 0,011	1 * 0,011
Mo	02.01.2012	7,5 * 0,000	6 * 0,000	8 * 0,000
Di	03.01.2012	8,5 * 0,006	3 * 0,006	4 * 0,006
Mi	04.01.2012	7 * 0,015	5,5 * 0,015	2 * 0,015

Using a conventional programming language, I would use two nested loops. An outside loop for the whole timeline and a nested one for the agents. Being new to QlikView, I still don´t understand how the logic works here. So, I´d appreciate any help from your side...

[Anonymous]

You can set up a table for the days (of the week I am assuming, but it could be over all time) that show the factor:

Factors:
LOAD * INLINE [
    WeekDay, Factor
    So, 2.5
    Mo, 7.5
    Di, 8.5 
];

... etc ... Note that I'm not setting up with comma seperate decimal places in this example.

When you load in the transactions create a unique key and a WeekDay field:

TransTable:
LOAD
    RowNo() AS TransKey,
    WeekDay(TransDate) AS WeekDay,
    Value
FROM DataSource ....

You can then join onto your transactions table against WeekDay:

LEFT JOIN (TransTable) LOAD
    WeekDay(TransDate) AS WeekDay,
    Factor
RESIDENT Factors;

This new Factor field can then be used in a calculation on the transaction table

LEFT JOIN (TransTable) LOAD
   TransKey,
   Value*Factor AS AmendedValue
RESIDENT TransTable;

And the Factors table can finally be dropped.

DROP TABLE Factors;

Hope this helps.

Jonathan

[chematos]

If you have in the same table a field for persons and a date field, you only need to join multipliers by weekday and make the calculation.

Do you have people transactions by day?

Could you copy the structure of your fact table?

If you can upload a little document with a version, better than anything

[#6]

Hello,

I´ve uploaded the QV document. I´m not sure if you can open it - since I´m using the personal edition.

[#7]

Hello José,

How can I join the two tables when there are weekdays for 365 dates a year (with duplicates) in one table and the other table doesn´t have a column named "weekday" but 7 columns for "Mo" through "So"? Moreover I have to select the right factor for the correspondig weekday for each date and each agent...

Date	Factor
01.01.2012	1,6
02.01.2012	2,5
03.01.2012	3,4

Agent	Mo	Di	Mi	Do	Fr	Sa	So
Peter	7,5	0,8	5,6	0,7	1,1	6,3	9,3
Paul	3,7	3,5	4,7	7,5	7,4	6,3	2,5
Mary	4,9	3,7	0,4	8,5	2,4	7,5	7,3

[chematos]

See that in Temp1 I use a field 'Name' that you need to create, that field should contain the names of the agents, and Value must be the value by day.

Then, there is a left join, so you can use the Factor to multiply with the value by day and Agent, that's what I'm doing in F_TABLE.

Temp1:

LOAD Weekday,

     Date,

     Name,

     Value

FROM

[..\Datenimport\Agents.xlsx]

(ooxml, embedded labels, table is Result);

left Join

LOAD WeekDay(Date) AS DayOfWeek,

     Factor

FROM

Agents.xlsx

(ooxml, embedded labels, table is Timeline);

F_TABLE:

NoConcatenate

LOAD *,

Value * Factor as Result

Resident Temp1;

DROP Table Temp1;

[#9]

Hello,

I think that I´m really close to the solution. The following is the code:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';
/* -------------------------------------------------------------------------------- */
Directory [..\Datenimport];
TimeLine:
LOAD Date,
     LEFT(WeekDay(Date), 2) AS WeekDay,
     Value
FROM
[Agents_2.xlsx]
(ooxml, embedded labels, table is TimeLine);
Factors:
LOAD Agent, 
     WeekDay, 
     Factor
FROM
[Agents_2.xlsx]
(ooxml, embedded labels, table is Factors);
/* -------------------------------------------------------------------------------- */
JoinTable:
LOAD Date,
     WeekDay,
     Value
RESIDENT
TimeLine;
LEFT JOIN
LOAD
     Agent,
     Factor,
     Value * Factor AS Result
RESIDENT Factors;

The two tables are joined correctly - thanks to using "LEFT(WeekDay(Date), 2) AS WeekDay". The only thing that still doesn´t work, is the calculation of the formula "Value * Factor AS Result" since the field "Value" is not known. Any idea how I could fix this?

[chematos]

Yes, use this after

/* ---------------------------------------------------------

JoinTable_1:
LOAD Date,
     WeekDay,
     Value
RESIDENT
TimeLine;
LEFT JOIN
LOAD

     Weekday
     Agent,
     Factor
RESIDENT Factors;

JoinTable:

NoConcatenate

Load *,

Value * Factor AS Result

RESIDENT JoinTable_1;

DROP TABLE JoinTable_1;

DROP TABLE TimeLine;

DROP TABLE Factors;