Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
There are two excel sheets SheetA and SheetB. SheetA have dates from 22/07/2015 to 16/11/2015. SheetB have dates from 04/08/2015 to 25/11/2015.
I have using below script to load both SheetA and SheetB dates.
MinMax:
LOAD
Max(TIMESTAMP([Date Closed])) AS MaxDate,
Min(TIMESTAMP([Date Closed])) AS MinDate
FROM
[..\..\Source\ClosedData.xlsx]
(ooxml, embedded labels, table is [SheetA]);
Concatenate
LOAD
Max(TIMESTAMP([Date Closed])) AS MaxDate,
Min(TIMESTAMP([Date Closed])) AS MinDate
FROM
[..\..\Source\ClosedData.xlsx]
(ooxml, embedded labels, table is [SheetB]);
but after reloading the dates are showing only from 22/07/2015 to 16/11/2015. I,e SheetA dates are only loading. SheetB is not loading.
Please can anyone suggest me is there anything wrong in script?
Thanks.
Did you try to load the SheetB Individually?
And,
Did you try to load the 2 sheets without Max or Min? is that worked?
if so, in the Next Step, try with
Load
Distinct
Date(Max([Closed Date])) as MaxDate,
Date(Min([Closed Date])) as MinDate
Resident MinMax;
And your MinMax table is showing exactely one or two rows?
What are you doing with this table afterwards? PEEK()ing a single row?
edit:
I assume you want to in fact concatenate the two excel tables, then create the MinMax table from the complete source table.
try it one by one and see if both are working fine?
Hi,
What is it you are trying to achieve. Like swuehl mentions i would expect a table with two rows in.
Mark
Tried with concatenate two excel tables. but not working. still showing only SheetA dates.
Actually before concatenating two excel tables below is the script that I used
MinMax:
LOAD
Max(TIMESTAMP([Date Closed])) AS MaxDate,
Min(TIMESTAMP([Date Closed])) AS MinDate
FROM
[..\..\Source\ClosedData.xlsx]
(ooxml, embedded labels, table is [SheetA]);
Concatenate
LOAD
Max(TIMESTAMP([Date Closed])) AS MaxDate,
Min(TIMESTAMP([Date Closed])) AS MinDate
FROM
[..\..\Source\ClosedData.xlsx]
(ooxml, embedded labels, table is [SheetB]);
/* STORE earliest and latest dates in variables */
LET varMinDate = Num(Peek('MinDate', -1, 'MinMax'));
LET varMaxDate = Num(Peek('MaxDate', -1, 'MinMax'));
/* STORE todays date in variable */
LET varToday = Num(Today());
QuarterMap:
MAPPING LOAD * INLINE [
Month, Quarter
1, Q1
2, Q1
3, Q1
4, Q2
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];
/* Autogenerate every date between varMinDate and varMaxDate while creating calendar fields */
Calendar:
LOAD*,
FiscalYear & '/ W' & FiscalWeek AS FiscalYearWeek;
LOAD*,
YearStart(Date,0,7) AS FiscalYearStart,
YearName(Date,0,7) AS FiscalYear,
AUTONUMBER(Year(Date) & Week(Date)) AS FiscalWeekSerial,
ROUND(num(([Date]+184-(MakeDate(Year(([Date]+184)-mod(([Date]-2),7)+3), 1, (mod(([Date]-2),7)+1) )-10))/7-0.5,'#,##0')) AS FiscalWeek,
'Q' & CEIL(if(num(Month(Date))<7,num(Month(Date))+6,num(Month(Date))-6)/3) AS FiscalQuarter;
LOAD
Timestamp(Date) as Date_TS,
Date#(Date(Date)) AS Date,
IF(Date = Today(),1) AS Today,
week(Date) AS Week,
Year(Date) AS Year,
Month(Date) AS Month,
Day(Date) AS Day,
WeekDay(Date) AS WeekDay,
ApplyMap('QuarterMap', Num(Month(Date)), Null()) AS Quarter,
Date(MonthStart(Date),'MMM-YYYY') AS MonthYear,
Week(Date) & '-' & Year(Date) AS WeekYear,
InYearToDate(Date, $(varToday), 0) * -1 AS CurYTDFlag,
InYearToDate(Date, $(varToday), -1) * -1 AS LastYTDFlag;
LOAD
Date($(varMinDate) + RecNo() - 1) AS Date
AUTOGENERATE ($(varMaxDate) - $(varMinDate)+1);
DROP TABLE MinMax;
STORE Calendar INTO C:\Qlikview\QVD\Calendar.qvd;
DROP TABLE Calendar;
Hi Amelia,
MinMax:
LOAD
Max(TIMESTAMP([Date Closed])) AS MaxDate,
Min(TIMESTAMP([Date Closed])) AS MinDate
FROM
[..\..\Source\ClosedData.xlsx]
(ooxml, embedded labels, table is [SheetA]);
Concatenate
LOAD
Max(TIMESTAMP([Date Closed])) AS MaxDate,
Min(TIMESTAMP([Date Closed])) AS MinDate,
' ' as Junk
FROM
[..\..\Source\ClosedData.xlsx]
(ooxml, embedded labels, table is [SheetB]);
Drop Field Junk;
Try this script.
Due to same no. of fields and same fields in both the files you are including. Qlikview ignoring SheeB's Data.
Regards,
Kiran
both sheets are like that only we do not have common fields in sheets. would it not be possible with out same fields in both sheets ?
You are including same no. of Fields from both the sheets and aliasing those fields with same names.
You need to create ' ' as Junk field (Dummy Field) in SheetB (Bottom table) loading script.
That dummy field will make a diffrence for no. of fields.
Try the script shared by me. I think it should work.
Regards,
Kiran
tried it with junk field but not working.
Why would QlikView ignore Sheet B's data if they have the same number of fields in both files?