9 Replies Latest reply: Sep 8, 2016 6:18 AM by Simon Hynd RSS

    Loading an excel spreadsheet as a crosstable

    Simon Hynd

      I have an excel spreadsheet (attached) with account balances for each month for the past 4 years (1month per column, 1 account code per line).

       

      Column A - Account Name (Accountancy Fees to Work in Progress

      Column B - Account Ref

      Column C - 01/01/16

      Column D - 01/02/16

      ...........

      Column AX - 01/12/13

       

      Row 1 Headers

      Row 2 onwards is each account.

       

      I want to load it as:

      Account Name (1st Account Name), Account Ref, Value(01/01/16)

      Account Name (1st Account Name), Account Ref, Value(01/02/16)

      ........

      Account Name (1st Account Name), Account Ref, Value(01/12/13)

      Account Name (Next), Account Ref, Value(01/01/16)

      Account Name (Next), Account Ref, Value(01/02/16)

      ........

      Account Name (Next), Account Ref, Value(01/12/13)

      .......

      Account Name (Last Account Name), Account Ref, Value(01/01/16)

      Account Name (Last Account Name), Account Ref, Value(01/02/16)

      ........

      Account Name (Last Account Name), Account Ref, Value(01/12/13)



      Also, I'm comparing the data with other data for the corresponding months using the field 'TransDate' as the key that links to other tables so the dates need to load into the 'TransDate' field.


      I assume I would need the CrossTable function to load the data the way I want it? If so how would I do it please?



      Thanks very much for your assistance.




        • Re: Loading an excel spreadsheet as a crosstable
          Gysbert Wassenaar

          Something like this:

           

          Temp:

          CrossTable(TempDate, Value, 2)

          LOAD

            *

          FROM

            [LIB://MyFolder/Croos Table Sage.xlsx] (ooxml, embedded labels, table is Sheet1)

            ;

           

          Result:

          LOAD

            *,

            Date(Num#(TempDate)) as Date

          RESIDENT

            Temp;

           

          DROP TABLE Temp;

          DROP FIELD TempDate; 

            • Re: Loading an excel spreadsheet as a crosstable
              Simon Hynd

              Sorry, I'm really struggling with this, seems to be a bug in the code (no doubt I've entered it wrong).

               

              I've attached the app as I've been playing around with this all day and not getting anywhere.

               

              Thanks for your assistance by the way,

                • Re: Loading an excel spreadsheet as a crosstable
                  Gysbert Wassenaar

                  There's a semicolon where there shouldn't be one:

                  Temp:

                  CrossTable(Date,Value,2)

                  LOAD NAME,

                      `ACCOUNT_REF`,

                      `01/01/2016`,

                      `01/02/2016`,

                      `01/03/2016`; <-- this one

                  FROM

                  [lib://Downloads/Downloads\Croos Table Sage.xlsx]

                  (ooxml, embedded labels, table is Sheet1);

                  Then in the next load you reference a field TempDate which won't exist because you gave it the name Date when you created the Temp table above:

                  Result:

                  Load

                  *,

                  Date(Num#(TempDate)) as Date

                  RESIDENT

                  Temp;

                   

                   

                  DROP TABLE TEMP;

                  DROP FIELD TempDate;

                  You can rename Date as TempDate in the first load or use Date in both loads. As long as you use the same name in both fields it doesn't matter what name you use.

                    • Re: Loading an excel spreadsheet as a crosstable
                      Simon Hynd

                      Thanks Gysbert,

                       

                      I have made that change, it was then not finding the fields '01/01'2016', '01/02/2016' and '01/03/2016' from the below script:

                       

                      LOAD NAME,

                          `ACCOUNT_REF`,

                          `01/01/2016`,

                          `01/02/2016`,

                          `01/03/2016`

                       

                      So I changed the fields in Excel from dates to text but now I'm getting error:

                       

                      The following error occurred:

                      Field not found - <TempDate>

                      The error occurred here:

                      Result: Load *, Date(Num#(TempDate)) as Date RESIDENT Temp

                        • Re: Loading an excel spreadsheet as a crosstable
                          Gysbert Wassenaar

                          The bold field names in these two lines should be the same:

                           

                          CrossTable(Date,Value,2)

                           

                          Date(Num#(TempDate)) as Date

                            • Re: Loading an excel spreadsheet as a crosstable
                              Simon Hynd

                              Hi Gysbert, sorry to keep troubling you.

                               

                              I've added the load script to the larger app I'm working on and made a slight tweak to stop it dropping the TransDate field which is upsetting the other data I have.

                               

                              The problem I'm having now is that the data is getting loaded in but does not correlate to any dates. When i make a bar chart the data sits outside the timescale on the chart. I don;t think it is converting the Dates correctly.

                               

                              Script is below:

                               

                              Temp:

                               

                              CrossTable(TransDate,Value,2)

                               

                              LOAD NAME,

                                  `ACCOUNT_REF`,

                                  `01/01/2016`,

                                  `01/02/2016`,

                                  `01/03/2016`,

                                  `01/04/2016`,

                                  `01/05/2016`,

                                  `01/06/2016`,

                                  `01/07/2016`,

                                  `01/08/2016`,

                                  `01/09/2016`,

                                  `01/10/2016`,

                                  `01/11/2016`,

                                  `01/12/2016`,

                                  "01/01/2015",

                                  "01/02/2015",

                                  "01/03/2015",

                                  "01/04/2015",

                                  "01/05/2015",

                                  "01/06/2015",

                                  "01/07/2015",

                                  "01/08/2015",

                                  "01/09/2015",

                                  "01/10/2015",

                                  "01/11/2015",

                                  "01/12/2015",

                                  "01/01/2014",

                                  "01/02/2014",

                                  "01/03/2014",

                                  "01/04/2014",

                                  "01/05/2014",

                                  "01/06/2014",

                                  "01/07/2014",

                                  "01/08/2014",

                                  "01/09/2014",

                                  "01/10/2014",

                                  "01/11/2014",

                                  "01/12/2014",

                                  "01/01/2013",

                                  "01/02/2013",

                                  "01/03/2013",

                                  "01/04/2013",

                                  "01/05/2013",

                                  "01/06/2013",

                                  "01/07/2013",

                                  "01/08/2013",

                                  "01/09/2013",

                                  "01/10/2013",

                                  "01/11/2013",

                                  "01/12/2013"

                                

                                 

                              FROM [lib://QlikDatabaseFolder/Sage\Sage Nominal Balances.xlsx]

                              (ooxml, embedded labels, table is NomBals);

                               

                              Result:

                              Load

                              *,

                              Date(Num#(TransDate)) as Date

                              RESIDENT

                              Temp;

                    • Re: Loading an excel spreadsheet as a crosstable
                      Simon Hynd

                      Thanks very much, works great! I owe you a beer!!