Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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.
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)
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
;
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 ... " - ").
Hi, ***that field**** shouldn't be?:
Month(date(date#(@5),'DD MMM YYYY')) as MONTH
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 ....
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.
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.
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