Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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

Re: Concatenate is not working? urgent help please

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;

16 Replies
MVP
MVP

Re: Concatenate is not working? urgent help please

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.

balrajahlawat
Esteemed Contributor

Re: Concatenate is not working? urgent help please

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

mark6505
Valued Contributor III

Re: Concatenate is not working? urgent help please

Hi,

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

Mark

Not applicable

Re: Concatenate is not working? urgent help please

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
Contributor III

Re: Concatenate is not working? urgent help please

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

Re: Concatenate is not working? urgent help please

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
Contributor III

Re: Concatenate is not working? urgent help please


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

Re: Concatenate is not working? urgent help please

tried it with junk field but not working.

Luminary
Luminary

Re: Concatenate is not working? urgent help please

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