Crosstable problems

[#1]

Hi all,

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

Client	01/11/2005	01/12/2005	01/01/2006	01/02/2006	01/03/2006	01/04/2006	01/05/2006	01/06/2006	01/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

[#2]

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

[#3]

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?

[jbeierschmitt]

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

[jbeierschmitt]

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

[jbeierschmitt]

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

[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

[renjithpl]

I think, you can try,

Date(Sdate, 'DD-MMM-YYYY') as Sdate

Hope this helps.

Regards

REn

[#9]

Hi,

I see a different code for crosstable in your post.

Did you try this

CrossTable

(CrossDate, AdValorum)

LOAD

*

FROM xyz.xls;

[#10]

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;