4 Replies Latest reply: Jan 12, 2012 9:40 AM by Steven Eysenbrandts RSS

    Date problem

      Hello,

       

      I have 2 tables (Table VAT Entry en Table Intrastat) that have several thousands of lines of data from purchases etc...

       

      Now I want to create a table that compares the purchases per month from the 2 tables. I have a TableA_Date field and a TableB_Date field. How do I combine these in 1 table so the both tables can be compared?

       

      Regards,


      Steven

        • Date problem

          I guess its more complicated than it sounds... 31 views and no reply

            • Date problem
              Stefan Wühl

              There are probably some different approaches.

               

              One simple would be to create a month field from the Date fields in both tables with same name, linking both tables.

               

              Then create a table with month as dimension and two expressions summing the values you want to compare.

              • Date problem
                Rob Cleek

                Throw some data at us so we can see what you're looking to do.  It doesn't sound hard, but at the same time...you never know till you see it.    There's many ways to accomplish it.  One way is to just join the two tables together based on date and compare the data that way.  but I suspect there's a better way to do it.

              • Re: Date problem
                Miguel Angel Baeyens de Arce

                Hi Steven,

                 

                I would concatenate both tables renaming as needed so they share the most relevant field names (date, amount, quantity), then create a master calendar from these dates.

                 

                FactTable:
                LOAD Date,
                     Amount,
                     Quantity,
                     Customer,
                     Country,
                     'VAT Entry' AS Fact_Type
                FROM ... // file or database source for Table_VAT_Entry
                CONCATENATE Table_VAT_Entry LOAD Date,
                     Amount,
                     Quantity,
                     Customer,
                     Country,
                     'Intrastat' AS Fact_Type
                FROM .. // file or database source for Table Intrastat
                

                 

                If source files or database have different file names, you will need to rename them.

                 

                Hope that helps.

                 

                Miguel

                • Date problem
                  Eva Polini

                  Could you give an example?

                    • Re: Date problem

                      This is the load for the intrastat

                       

                      Journal:

                      LOAD "Country_Region Code" AS "J_Country_Region Code",

                          Amount AS "J_Amount",

                          "Document No_" AS "IL_Shipment No_",

                          "Item No_" AS "J_Item No_",

                          "Transaction Specification" AS "J_Transaction Specification",

                          "Date" AS "J_Date";

                      SQL SELECT "Country_Region Code",

                          Amount,

                          "Document No_",

                          "Item No_",

                          "Transaction Specification",

                          "Date"

                      FROM FILE;

                       

                      And a selection of the VATEntry:

                      VATEntry:

                      LOAD Amount AS "VE_Amount",

                          Base AS "VE_Base",

                          "Base Before Pmt_ Disc_" AS "VE_Base Before Pmt_ Disc_",

                        //key voor Vendor

                          "Bill-to_Pay-to No_" AS V_No_,

                          "Country_Region Code" AS "VE_Country_Region Code",

                          if([Country_Region Code]='BE',0,1) AS VE_Country_Selection,

                          "Document No_" AS "VE_Document No_",

                          "Document Type" AS "VE_Document Type",

                          "Enterprise No_" AS "VE_Enterprise No_",

                          "EU 3-Party Trade" AS "VE_EU 3-Party Trade",

                          "Gen_ Bus_ Posting Group" AS "VE_Gen_ Bus_ Posting Group",

                          "Posting Date" AS "VE_Posting Date";

                      SQL SELECT Amount,

                          Base,

                          "Base Before Pmt_ Disc_",

                          "Bill-to_Pay-to No_",

                          "Country_Region Code",

                          "Document No_",

                          "Document Type",

                          "Enterprise No_",

                          "EU 3-Party Trade",

                          "Gen_ Bus_ Posting Group",

                          "Posting Date",

                       

                      FROM FILE;

                      I want to compare sum(VE_Amount) and sum(J_Amount) for each month ([VE_Posting Date] and [J_Date]). If I simply create a field Month there is a loop due to other tables.