Question about load data from two tables with SQL.

[Anonymous]

Hello,

Escuse me, I have a problem. this is not many time I start with QlikView and I have many question.

I need to load rows data of quantity and amount per article but I have this data in two tables with different name and different fields  names.

I load with SQL anfd I user the table item.

So:

ODBC CONNECT TO DATA01;

// First table with rows with quantity and Amount
Table1:
SQL SELECT
    Table1_ItemCode AS Table3_ItemCode,
    Table1_Quantity,
    Table1_Amount
FROM Table1;

// Second table with rows with quantity and Amount
Table2:
SQL SELECT
    Table2_ItemCode AS Table3_ItemCode1,
    Table2_Quantity,
    Table2_Amount
FROM Table2;

// Item Table

Table3:
SQL SELECT
    Table3_ItemCode,
    Table3_ItemCode AS Table3_ItemCode1,
    Table3_ItemDescription,

I must do a temporary table? How?

I dont want use Synth Key also (I have use a alias in Table3 for Table 2 but I dont know if this is the right way).

Thank you very mych!!!

Best regards.

[rubenmarin · Accepted solution]

Hi Giuseppe, you can concatenate table 1 and table 2, table 3 can be a left join with the result table if ItemCode is unique, or leave it in a separated table if there are duplicated ItemCode or if is better for the model to keep in another table:

FactTable:

SQL SELECT

    Table1_ItemCode AS ItemCode,

    Table1_Quantity as Quantity,

    Table1_Amount as Amount

FROM Table1;

Concatenate (FactTable)

LOAD *;

SQL SELECT

    Table2_ItemCode AS ItemCode,

    Table2_Quantity as Quantity,

    Table2_Amount as Amount

FROM Table2;

Left Join(FactTable) <-- Change this line by "Items:" if you want it in separated table

LOAD *;

SQL SELECT

    Table3_ItemCode as ItemCode,

    Table3_ItemDescription as Description

FROM ItemTable;

[maxgro]

do you want to add articles in table2 to articles in table1 (like a union all in SQL)?

if yes use a concatenate

[Anonymous]

Thank you Ruben and Massimo!

Now is all okay!

I need to lear better QLV Scripting but now is very clear!

Best regards

[maxgro]

for some help about loading data in QlikView you can read this

LOAD data into QlikView

[Anonymous]

Thank you for good suggestion Massimo!

[Anonymous]

Can I ask another think?

I have both in Table1 and in Table2 a date now (DATAREG).

I created a master calendar but it dont work proprely if I call the master calendar creation after load the table.

Maybe I must order for date filed the FactsTable after I created this?

I tryed but I dont know where I can write the ORDER BY statement, if I write this at the end of the load og TableItem ad before the master calendar I have an error.

Thank you again!

[maxgro]

You should have (from Ruben answer) a FactTable with a DATEREG field and the same field (DATEREG) in MasterCalendar to link with FactTable.

There is no need to order the table, you usually order the charts in the sort tab.

If I missing something, please post the scriptor, better, a small example with data

[Anonymous]

Thank you for your answer Massimo.

Yes, I do what you and Ruben write but dont work.

So, the date is DATARG and this is the script:

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='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';

// Data from System i (AS/400)

ODBC CONNECT TO DATA01;

//-------- Start Multiple Select Statements ------

Facts:
SQL SELECT
    Table1_ItemCode AS Table3_ItemCode,
    DATE(timestamp_format(digits(Table1_Date),'YYYYMMDD' )) AS DATARG,
    Table1_Quantity AS Quantity,
    Table1_Turnover AS Turnover
FROM Table1;

Concatenate(Facts)
LOAD *;
SQL SELECT
    Table2_ItemCode AS Table3_ItemCode,
    DATE(timestamp_format(digits(Table2_Date),'YYYYMMDD' )) AS DATARG,
    Table2_Quantity AS Quantity,
    Table2_Turnover AS Turnover
FROM Table2

Left Join(Facts)
LOAD *;
SQL SELECT
    Table3_ItemCode,
    Table3_ItemDescription,
    FROM Table3

//-------- End Multiple Select Statements ------

$(Include=..\inclusioni\mastercalendar.txt);

The mastercalendera. txt that I include is this:

let varMinDate = num(peek('DATARG',0,'Facts'));
let varMaxDate = num(peek('DATARG',-1,'Facts'));

TempCalendar:
Load

date($(varMinDate) + rowno() -1) as TempDate
autogenerate $(varMaxDate) - $(varMinDate) +1;

MasterCalendar:
LOAD
date(TempDate) AS DATARG,
week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
inyeartodate (TempDate,Date(Today()), 0) *-1 as CYTDFlag,
inyeartodate (TempDate,Date(Today()), -1) *-1 as LYTDFlag,
inyear (TempDate,Date(Today()), 0) *-1 as CYFlag,
inyear (TempDate,Date(Today()), -1) *-1 as LYFlag,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
'T' & ceil(month(TempDate)/3) as Quarter,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
weekday(TempDate) AS WeekDay

RESIDENT
TempCalendar
ORDER BY
TempDate Asc;

DROP TABLE TempCalendar;

I have two problem:

- I dont have Year 2015 (I have rows with year 2015 in Table1 but not in Table2 and I have in Table1 date from 08/09/1992 but I see Year only from 1994, in Mastercalender Table I see date from 08/09/1992 to 10/02/2015, so I dont understand what Qlikview do and where QlikView find data with wrong month, I controlled the data both of Table1 and Table2 in AS/400 and these is okay);

- if I write a bar chart with Month in dimension I have the month from Jan to Dic and some data without Month and Quarter.

I attach an example screenshot:

Thank you again.

[rubenmarin]

Hi Giuseppe,

Try to set varMinDate and varMaxDate this way:

     TempDates:

     NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG;

     let varMinDate = num(peek('DATARG',0,'TempDates'));

     let varMaxDate = num(peek('DATARG',-1,'TempDates'));

     DROP Table TempDates;