Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Concatenated Tables not linking to Master Calendar

Hi Guys

I'm sure I'm missing something stupid. I've got a few csv files that I'm loading into QV with a "concatenate Load ..." script. The data comes in fine, but only the first file is linked to the Master Calendar that I've set up. What am I missing?

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Concatenated Tables not linking to Master Calendar

Never mind! Got it working!

Your suggestion pointed me in the right direction. What I did was ...

Add a dummy table with a fieldname MONTH.

Then concatenate load the tables - I name the whole set DATA_REST

AND ONLY THEN the master calendar script is loaded/referenced

then recall the DATA_REST with a new name

This new set is then mapped to the master calendar and all the dates are then mapped

Thanks for the quick help guys

9 Replies

Re: Concatenated Tables not linking to Master Calendar

If you concatenate tables the result is only one table. So there will be only one table to link with the master calendar table. If only data from the first source table is associated with data in the master calendar table then either then master calendar table doesn't contain the date from the other source tables or maybe the dates aren't date values, but text values.


talk is cheap, supply exceeds demand

Re: Concatenated Tables not linking to Master Calendar

Hi Samuel, how you know only the first file is linked? If it is because there are one table for each csv, then concatenate is not working, try concatenate (TableName)

Not applicable

Re: Concatenated Tables not linking to Master Calendar

Ok....it's definitely text values, but I'm converting it to a date in the load script ...

My Master calendar looks like below with the MONTH field being the key field that I'm using.

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

  Dual(Month, fMonth) as FMonth, // Dual fiscal month

  *;

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

  Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

  *;

Load Year(MONTH) as Year, // Your standard master calendar

  Month(MONTH) as Month,

  *;

Load Today() - MONTH as DaysAgo,

  12*(Year(Today())-Year(MONTH)) + Month(Today()) - Month(MONTH) as MonthsAgo,

  Today() - YearStart(Today(),0,$(vFM)) - MONTH + YearStart(MONTH,0,$(vFM)) as DaysAgoYTD,

  Mod(Month(Today())-$(vFM),12) - Mod(Month(MONTH)-$(vFM),12) as MonthsAgoYTD,

  *;

My concatenation looks like ....

Data_Rest:

Concatenate LOAD

FileName() as FILE,

@1 as FIN_YEAR,

@2 as COMPANY,

@3 as ACCOUNT,

@4 as CENTRE,

date(date#(@5),'DD MMM YYYY') as EFFECTIVE_DATE,

date(date#(@5),'DD MMM YYYY') as MONTH (***************this field********)

FROM

x:\filepath\ex*.csv

(txt, codepage is 1252, no labels, delimiter is ',', no quotes,no eof, filters(

Remove(Row, Pos(Top, 1))

))

where @2 = 2280

;

Not applicable

Re: Concatenated Tables not linking to Master Calendar

I've got a table box in the model that shows me all the data ... on the first table's data I can see the master calendar fields (FYear/FMonth etc) that maps correctly. On the rest of the data it's just nulls (or this ... " - ").

Re: Concatenated Tables not linking to Master Calendar

Hi, ***that field**** shouldn't be?:

Month(date(date#(@5),'DD MMM YYYY')) as MONTH

Not applicable

Re: Concatenated Tables not linking to Master Calendar

No, it's just semantics ... I just use MONTH instead of DATE, because of naming issues that inevitably come up. That MONTH field is a normal date field (supposed to be anyway). If I don't concatenate and load only one table, then the script is fine. As soon as I concatenate with more than one table, the calendar add-ons only map to the first file's data.

I was wondering if my master calendar shouldn't come after the concatenation, but then it's just ignored competely. Data comes in, but no calendar fields are mapped ....

Re: Concatenated Tables not linking to Master Calendar

Ok, can you try?:

Data_Rest:

Concatenate (Data_Rest) LOAD ....

If this gives you an error like "Table Data_Rest no exist" you can add before an inline table so QV finds a table to concatenate:

Data_Rest: LOAD * Inline [DumbField];

Concatenate (Data_Rest) LOAD ....

Also, you can check if MONTH values retrieved by other csv really exists in calendar (best compare them in number format). Maybe there are decimal values or QV conversion isn't working for any reason.

Not applicable

Re: Concatenated Tables not linking to Master Calendar

I'm afraid neither option worked ...

The first suggestion says "no such table" as you would've guessed.

The second suggestion responds saying "no MONTH field found". So I changed the [DummyField] to [MONTH] and it loaded fine, but of course there's no calendar info on the second file's data.

The fields in the csv files are in yyyymmdd format (and recognised as a number if opened in excel). If I load one file at a time, the master calendar works perfectly so I don't think it's got to do with the raw data.

Not applicable

Re: Concatenated Tables not linking to Master Calendar

Never mind! Got it working!

Your suggestion pointed me in the right direction. What I did was ...

Add a dummy table with a fieldname MONTH.

Then concatenate load the tables - I name the whole set DATA_REST

AND ONLY THEN the master calendar script is loaded/referenced

then recall the DATA_REST with a new name

This new set is then mapped to the master calendar and all the dates are then mapped

Thanks for the quick help guys

Community Browser