9 Replies Latest reply: Feb 8, 2011 4:23 AM by Paco Rios RSS

    Crosstable problems

    alan roche

      Hi all,

      I have a problem where I need to import data laid out in a spreadsheet see example

       

      Client01/11/200501/12/200501/01/200601/02/200601/03/200601/04/200601/05/200601/06/200601/07/2006
      Client 1£119,642.52£139,945.80£145,483.69£135,995.82£156,783.31£170,135.46£167,821.18£148,868.58£252,939.31
      Client 2£7,249.01£7,249.01£8,445.12£8,445.12£8,445.12£9,534.56£9,534.56£9,534.56£9,759.79


      I have used Crosstable to import the data

       

       

       

       

       

       

       

       



       

       

       

       

       

       

       

       

      CROSSTABLE

       

       

      LOAD

       

       

      [38657]

       

      ,

      [38687]

       

      ,

      [38718]

       

      ,

      [38749]

       

      ,

      [38777]

       

      ,

      [38808]

       

      ,

      Client,(CrossDate, AdValorum)



      etc etc.

      I am then trying to build a chart with Ad valorum on the X axis and Date on the Y axis, I can build the chart but I can't convert the date to a true date using " Date(CrossDate)" or can split the Date into Days and months etc. Can anyone see what the problem is?

       

      Thanks in advance

      Alan

        • Crosstable problems
          Amit Somani

          Hi Alan,

          After loading the data using CROSS TABLE load the same data into a different table like, (Assumed that Cross tab table is ready)

          Tmp:
          Load Date(Date#(CrossDate,'YYYYDDMM'),'MM/DD/YYYY') as CrossDate,
          *
          Resident <CROSS TABLE NAME>

          Dates:
          Load DISTINCT
          CrossDate,
          Year(CrossDate) as Year,
          Month(CrossDate) as Month
          RESIDENT Tmp;

          Hope this helps.

          Regards,
          Amit

            • Crosstable problems
              alan roche

              Hi Amit,

              Thanks for your help unfortunately that didnt work. The problem is the date field values ie. [38687] or [38961] can't seem to be converted into a real date after being imported using Crosstable function. I am sure there is a simple way of doing this as the problem must come up a lot.

              I'll keep trying but seem to be stuck

              Regards Alan

              PS: Is there anyway we can post sample qvws on the boards?

               

                • Crosstable problems
                  Jim Beierschmitt

                  I have the same problem - I cannot transform the crosstable date field into a date format - any updates on this thread?

                    • Crosstable problems
                      Jim Beierschmitt

                      It appears the problem is with the date field value being read after a crosstable function as [40119] - I used the purgechar function to remove the issue and successfully read as a date value:

                      = date(purgechar(DateTmp,'[]'),'MM/DD/YYYY')

                       

                      ZFORECAST:

                      Load

                       

                       

                      date

                       

                      (purgechar(DateTmp,'[]'),'MM/DD/YYYY') as DateID,

                       

                      *

                      Resident

                       

                       

                      ZFORECASTTEMP;

                      Jim

                      • Crosstable problems
                        Jim Beierschmitt

                        It appears the problem is with the date field value being read after a crosstable function as [40119] - I used the purgechar function to remove the issue and successfully read as a date value:

                        = date(purgechar(DateTmp,'[]'),'MM/DD/YYYY')

                         

                        ZFORECAST:

                        Load

                         

                         

                        date

                         

                        (purgechar(DateTmp,'[]'),'MM/DD/YYYY') as DateID,

                         

                        *

                        Resident

                         

                         

                        ZFORECASTTEMP;

                        Jim

                          • Crosstable problems
                            jaspal.icon

                            Hello! Thanks your post helped me to solve crosstable problem,

                            I also getting the date as 40119.000000 , Then I reforma the date in excel sheet from 01-Mar-2010 to 01/03/2010 then it giving the 40119, then again after lot of try using Date# etc. it was not workign at all, then I tried this Date(Left(Sdate,2)&','&Mid(Sdate,3,3)) Then this helped em to get the date out of it. But again I was searchign for alternative, as I was not sure that trim function will always give correct result.

                            Thanks

                    • Crosstable problems
                      takkuldeep

                      Hi,

                      I see a different code for crosstable in your post.

                      Did you try this

                       

                       

                       

                       

                       

                       

                       

                       



                       

                       

                      CrossTable

                       

                       

                      (CrossDate, AdValorum)

                      LOAD

                       

                       

                      *

                      FROM xyz.xls;



                       

                       





                      • Crosstable problems
                        Paco Rios

                        You must evaluate your date:

                         

                        Map_Extradata:

                        Directory;

                        CrossTable(YourDate, Data,3)

                        LOAD * FROM

                        [\\telesto\Base para QV\Balance & Cta. Resultados\Datos adicionales resultados.xlsx]

                        (ooxml, embedded labels, table is Plano);

                         

                        LOAD

                        Fila,

                        Concepto,

                        Empresa,

                         

                        Date(Num(Evaluate(YourDate) )) as "Posting Date",

                        Datos as Amount

                        Resident Map_Extrasata;

                        Drop Table Map_Extradata;