Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concatenate is not working? urgent help please

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.


1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

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;

View solution in original post

16 Replies
swuehl
MVP
MVP

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.

Anonymous
Not applicable
Author

try it one by one and see if both are working fine?

Mark_Little
Luminary
Luminary

Hi,

What is it you are trying to achieve. Like swuehl mentions i would expect a table with two rows in.

Mark

Not applicable
Author

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;

kiranmanoharrode
Creator III
Creator III

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

Not applicable
Author

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 ?

kiranmanoharrode
Creator III
Creator III


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

Not applicable
Author

tried it with junk field but not working.

simondachstr
Luminary Alumni
Luminary Alumni

Why would QlikView ignore Sheet B's data if they have the same number of fields in both files?