Concatenate is not working? urgent help please

[#1]

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.

[thomaslg_wq]

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'));

[effinty2112]

I think adding and then removing the Junk field is a way to prevent automatic concatenatation rather than force it. No?

[effinty2112]

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;

[#5]

Tried. it is not working. but the Temp table is creating fine.

[settu_periasamy · Accepted solution]

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;

[Anonymous]

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

[#8]

I have tried reloading separately. then it worked.

Thanks to all