Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Not applicable

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

Highlighted
Not applicable

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?

Highlighted
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?

Highlighted
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

Highlighted
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

Highlighted
Partner
Partner

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

Highlighted
Specialist
Specialist

I think, you can try,

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

Hope this helps.

Regards

REn

Highlighted
Not applicable

Hi,

I see a different code for crosstable in your post.

Did you try this



CrossTable

(CrossDate, AdValorum)

LOAD

*

FROM xyz.xls;







Highlighted
Not applicable

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;