Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

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

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
9 Replies

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

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

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

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

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

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.

neetha_p
Honored Contributor

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

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

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

Thanks All,

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

How do I get the latest date from this?

neetha_p
Honored Contributor

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

Hi Rekha,

Can you be specific about your requirement?

Not applicable

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

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

neetha_p
Honored Contributor

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

Hi Rekha,

Use below in script:

Max(Date) as MaxDate;

Regards

Neetha

neetha_p
Honored Contributor

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

And

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

Load *

resident tablename

where Date = $( vMaxDate );

Community Browser