9 Replies Latest reply: Jan 14, 2015 10:19 AM by Samuel Mostert RSS

    Concatenated Tables not linking to Master Calendar

    Samuel Mostert

      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?

        • Re: Concatenated Tables not linking to Master Calendar
          Gysbert Wassenaar

          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.

            • Re: Concatenated Tables not linking to Master Calendar
              Samuel Mostert

              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

              ;

                • Re: Concatenated Tables not linking to Master Calendar
                  Ruben Marin

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

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

                    • Re: Concatenated Tables not linking to Master Calendar
                      Samuel Mostert

                      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
                          Ruben Marin

                          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.

                            • Re: Concatenated Tables not linking to Master Calendar
                              Samuel Mostert

                              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.

                              • Re: Concatenated Tables not linking to Master Calendar
                                Samuel Mostert

                                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

                      • Re: Concatenated Tables not linking to Master Calendar
                        Ruben Marin

                        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)