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.
When this happens, usually is because of the table is concatenating with the previous one, so you need to be carefull with that and use NoConcatenate as below code
JoinTable_1:
NoConcatenate LOAD Date, WeekDay, Value RESIDENT TimeLine; LEFT JOIN LOAD WeekDay, Agent, Factor RESIDENT Factors;
JoinTable: NoConcatenate Load *, Value * Factor AS Result RESIDENT JoinTable_1;
