Solved: Question about load data from two tables with SQL. - Page 2 - Qlik Community

Anonymous · ‎2015-02-10

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.

Anonymous · ‎2015-02-11

Hi Ruben,

so my mastercalendar now is:

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;

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;

but if I load the data from Qlikview I have this error:

Script Error

TempCalendar:
Load

date(42046 + rowno() -1) as TempDate
autogenerate 37148 - 42046 +1

Please can you help me, maybe I dont understand what I must to do.

Thank you very much!

rubenmarin1 · ‎2015-02-11

Hi, not sure why, but is getting switched MinDate and MaxDate, lets give another name to the TempDates field:

TempDates:

NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG_tmp;

let varMinDate = num(peek('DATARG_tmp',0,'TempDates'));
let varMaxDate = num(peek('DATARG_tmp',-1,'TempDates'));

DROP Table TempDates;

If continues switching varMinDate and varMaxDate let's try to sort TempDates descending, maybe date format sorts different but it will be weird:

TempDates:

NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG_tmp desc;

let varMinDate = num(peek('DATARG_tmp',0,'TempDates'));
let varMaxDate = num(peek('DATARG_tmp',-1,'TempDates'));

DROP Table TempDates;

Anonymous · ‎2015-02-11

This dont work, if I use:

TempDates:

NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG_tmp;

let varMinDate = num(peek('DATARG_tmp',0,'TempDates'));
let varMaxDate = num(peek('DATARG_tmp',-1,'TempDates'));

DROP Table TempDates;

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 the error:

Field not found
TempDates:

NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG_tmp

I must to use a alias?

How?

Thank you!

rubenmarin1 · ‎2015-02-11

Sorry, the Order By should be:

NoConcatenate LOAD Distinct DATARG as DATARG_tmp Resident Facts Order By DATARG;

Anonymous · ‎2015-02-11

Not a problem Ruben, I thank you very much!

Unfortunatelly this dont work with:

TempDates:

NoConcatenate LOAD Distinct DATARG as DATARG_tmp Resident Facts Order By DATARG;

let varMinDate = num(peek('DATARG_tmp',0,'TempDates'));
let varMaxDate = num(peek('DATARG_tmp',-1,'TempDates'));

DROP Table TempDates;

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 no error but I have the bar chart with this data:

So now I have many data without month.

I dont know what can I do!

rubenmarin1 · ‎2015-02-11

I don't know why but is taking xx/xx/2001 as MaxDate, Facts table is concatenated before creating calendar, isn't it?

Can you upload a sample or at least the reload log?

I tested with sample data and works:

Facts:

LOAD Date(DATARG) as DATARG;

LOAD * Inline [

DATARG

01/01/1992

11/02/2015

];

TempDates:

....

This returns me all dates from 01/01/1992 to 11/02/2015 with all his months, weeks, etc...

Anonymous · ‎2015-02-11

So, I repeat 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 mastercalendar:

TempDates:

NoConcatenate LOAD Distinct DATARG as DATARG_tmp Resident Facts Order By DATARG;

let varMinDate = num(peek('DATARG_tmp',0,'TempDates'));
let varMaxDate = num(peek('DATARG_tmp',-1,'TempDates'));

DROP Table TempDates;

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 work wit a desktop version and I dont know where log is stored, sorry.

rubenmarin1 · ‎2015-02-11

Hi, I can't take more info from the script, I needed the document to check what kind of dates has stored in DATARG.

You can comment quantity and turnover fields if you don't want to upload that data, I only need the dates from facts and calendar tables..

BTW, to create the log you need to check, in 'Configurations' menu-->Document Properties--> General Tab, check 'Generate log file'

It's created in the same folder than the qvw.

Anonymous · ‎2015-02-11

Hi, thank you.

I attach files without sensitive data.

rubenmarin1 · ‎2015-02-11

I'm still lost, DATARG has dates till 11/02/2015 and it really is date format, why is taking 25/05/2005 as max date?

Another way to retrieve varMaxDate and varMinDate:

TempDates:

LOAD Min(DATARG) as MinDATARG,

Max(DATARG) as MaxDATARG

Resident Facts;

let varMinDate = Num(FieldValue('MinDATARG', 1));
let varMaxDate = Num(FieldValue('MaxDATARG', 1));

DROP Table TempDates;