Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Crosstable problems

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

9 Replies
Not applicable
Author

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

Not applicable
Author

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
Contributor III
Contributor III

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

jbeierschmitt
Contributor III
Contributor III

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
Contributor III
Contributor III

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
Partner - Creator
Partner - Creator

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
Specialist
Specialist

I think, you can try,

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

Hope this helps.

Regards

REn

Not applicable
Author

Hi,

I see a different code for crosstable in your post.

Did you try this



CrossTable

(CrossDate, AdValorum)

LOAD

*

FROM xyz.xls;







Not applicable
Author

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;