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.
By doing as you do, you create 2 rows in your MinMax table, and then search only the first row with your peek function.
To get one only row in your MinMax table with real min and max values, concatenate before doing min and max functions as :
Temp_MinMax:
LOAD distinct
[Date Closed] as Date
FROM
[..\..\Source\ClosedData.xlsx]
(ooxml, embedded labels, table is [SheetA]);
Concatenate (Temp_MinMax)
LOAD distinct
[Date Closed] as Date
FROM
[..\..\Source\ClosedData.xlsx]
(ooxml, embedded labels, table is [SheetB]);
MinMax:
min(Date) as MinDate,
max(Date) as MaxDate
resident Temp_MinMax;
Drop table Temp_MinMax;
LET varMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET varMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
I think adding and then removing the Junk field is a way to prevent automatic concatenatation rather than force it. No?
Hi Amelia,
I would be tempted to use a temporary table like below. If it doesn't work exit before the concatenation and check that the Temp table was created.
MinMax:
LOAD
Max(TIMESTAMP([Date Closed])) AS MaxDate,
Min(TIMESTAMP([Date Closed])) AS MinDate
FROM
[..\..\Source\ClosedData.xlsx]
(ooxml, embedded labels, table is [SheetA]);
No Concatenate
Temp:
LOAD
Max(TIMESTAMP([Date Closed])) AS MaxDate,
Min(TIMESTAMP([Date Closed])) AS MinDate
FROM
[..\..\Source\ClosedData.xlsx]
(ooxml, embedded labels, table is [SheetB]);
//Exit Script;
concatenate(MinMax)
LOAD * resident Temp;
Drop table Temp;
Tried. it is not working. but the Temp table is creating fine.
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;
Hi Amelia,
22/07/2015 to 16/11/2015. SheetB have dates from 04/08/2015 to 25/11/2015.
In the final MinMax should display like this or how you want
Max Date Min Date
16/11/2015 22/07/2015
25/11/2015 04/08/2015
I have tried reloading separately. then it worked.
Thanks to all