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

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

Fri, 16 Jan 2015 10:38:30 GMT

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.

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

Gysbert_Wassenaar — Fri, 16 Jan 2015 10:51:03 GMT

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.

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

Fri, 16 Jan 2015 10:54:57 GMT

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?

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

ashwanin — Fri, 16 Jan 2015 11:07:20 GMT

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.

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

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

Anonymous — Fri, 16 Jan 2015 11:37:15 GMT

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

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

Fri, 16 Jan 2015 11:48:58 GMT

Thanks All,

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

How do I get the latest date from this?

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

Anonymous — Fri, 16 Jan 2015 11:50:25 GMT

Hi Rekha,

Can you be specific about your requirement?

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

Fri, 16 Jan 2015 11:52:38 GMT

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

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

Anonymous — Fri, 16 Jan 2015 11:59:22 GMT

Hi Rekha,

Use below in script:

Max(Date) as MaxDate;

Regards

Neetha

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

Anonymous — Fri, 16 Jan 2015 12:02:53 GMT

And

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

Load *

resident tablename

where Date = $( vMaxDate );