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

why the numbers under a date column is getting converted automatically to date

Hi,

I have an excel sheet with the below data and my requirement is to get the amount for the latest date according to the ERP ITEM Code

am trying to do cross table and load the data in qlikview,but automatically the values under the date column are getting converted to date when I am loading as in the attachment.

Can some one let me know how to get values under the date column .I have attached the excel sheet also.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

The dates ARE numbers. What you see is that the formatting has been removed. You should probably use the CrossTable function on such a data source.


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
Gysbert_Wassenaar

The dates ARE numbers. What you see is that the formatting has been removed. You should probably use the CrossTable function on such a data source.


talk is cheap, supply exceeds demand
Not applicable
Author

Hi,

Ya if you see in the Excel sheet,there are numerical values(amount) under every date column.when am loading the excel sheet those numbers are getting converted to date as you see in the screen shot.

Before I go to the cross table,i want to see the amount values under the date column visible.How can I do that?

ashwanin
Specialist
Specialist

Hi

This might be due to the excel format of cell. I just changed it to General in your provided file and see the date format disappears.

Capture.JPG

and for Date in row ,you can choose cross table function.

Anonymous
Not applicable
Author

Hi Rekha,

Try:

CrossTable:
CrossTable(Date, Values, 7)
LOAD State,
Region,
[Region Name],
Location,
[Item Name],
[ERP Item Code],
UOM,
[41974],
[41975],
[41976],
[41977],
[41978],
[41979],
[41980],
[41981],
[41982],
[41983],
[41984],
[41985],
[41986],
[41987],
[41988],
[41989],
[41990],
[41991],
[41992],
[41993],
[41994],
[41995],
[41996],
[41997],
[41998],
[41999],
[42000],
[42001],
[42002],
[42003],
[42004]
FROM

(
ooxml, embedded labels, table is Sheet1);

Table:
Load *,
Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')as Dates
Resident CrossTable ;

Regards

Neetha

Not applicable
Author

Thanks All,

Gysbert and Neethu I followed your approach and got the result.

How do I get the latest date from this?

Anonymous
Not applicable
Author

Hi Rekha,

Can you be specific about your requirement?

Not applicable
Author

Neetha there are many dates in the excel sheet right.now I want the records only the latest date available in the excel sheet

Anonymous
Not applicable
Author

Hi Rekha,

Use below in script:

Max(Date) as MaxDate;

Regards

Neetha

Anonymous
Not applicable
Author

And

Let  vMaxDate = Peek('Maxdate',0,'TableName');

Load *

resident tablename

where Date = $( vMaxDate );