Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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?
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.
and for Date in row ,you can choose cross table function.
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
(
Table:
Load *,
Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')as Dates
Resident CrossTable ;
Regards
Neetha
Thanks All,
Gysbert and Neethu I followed your approach and got the result.
How do I get the latest date from this?
Hi Rekha,
Can you be specific about your requirement?
Neetha there are many dates in the excel sheet right.now I want the records only the latest date available in the excel sheet
Hi Rekha,
Use below in script:
Max(Date) as MaxDate;
Regards
Neetha
And
Let vMaxDate = Peek('Maxdate',0,'TableName');
Load *
resident tablename
where Date = $( vMaxDate );