Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
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;
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!
Sorry, the Order By should be:
NoConcatenate LOAD Distinct DATARG as DATARG_tmp Resident Facts Order By DATARG;
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!
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...
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.
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.
Hi, thank you.
I attach files without sensitive data.
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;