Discussion Board for collaboration on QlikView Scripting.
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.
Go to Solution.
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.
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?
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.
CrossTable: CrossTable(Date, Values, 7) LOAD State, Region, [Region Name], Location, [Item Name], [ERP Item Code], UOM, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,  FROM (ooxml, embedded labels, table is Sheet1); Table: Load *, Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')as Dates Resident CrossTable ;
Gysbert and Neethu I followed your approach and got the result.
How do I get the latest date from this?
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
Use below in script:
Max(Date) as MaxDate;
Let vMaxDate = Peek('Maxdate',0,'TableName');
where Date = $( vMaxDate );