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;