topic Not getting proper values from excel in qlikview in QlikView

Not getting proper values from excel in qlikview

abc_18 — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

I am loading data from excel where column names are like OCT-14, Nov-14, when I load that data in qlikview, numeric values are not coming from that particular column.

in place of that I am getting different dates for that particular column.

output what I am getting in qlikview is like:-

Please suggest how to get correct value.

Re: Not getting proper values from excel in qlikview

Fri, 04 Mar 2016 07:37:14 GMT

Hi shikhakumari18‌

Can you share you excel file then i can check the problem.

If You want to get the values i have way to get the values i will show you down please check it....

Step 1 : Open the File in the File Wizard it appears this and having some null spaces into excel file

Step 2 :

After You click next in the wizard you will go to the enable transformation Step so click this and you will open new tab.

Step 3: After that this will appear and directly go to fill tab in the wizard this red rounded space has no data in the excel file so you want to fill that space with data Click Fill Button on the left side of the box which is in black rectangle ..

Step 4: After that an pop up box appears as fill cells after that click on the cell condition button which shows in the next step 5.

Step 5: When you click on the cell condition another pop up box appears as cell condition nothing to enter her just click OK and again OK in the cell condition also so now the data will be filled in the null spaces with data in the excel files of your data.

This will be helpful for the data otherwise send me your excel file i will send the solution for you

Re: Not getting proper values from excel in qlikview

Fri, 04 Mar 2016 07:47:48 GMT

Hi Kumari,Have a nice day.

what is your expected output.

Re: Not getting proper values from excel in qlikview

chrisjbishop — Fri, 04 Mar 2016 08:03:39 GMT

HI Kumari,

this looks like a data type issue, looks like the iport process into QV is assuming the "numeric" columns with "date headers" are dates.... I've just created a quick example:

2nd column has no formatting.

3rd column I've formatted the cells as numeric...

Import result:

hope this helps,

Chris.

Re: Not getting proper values from excel in qlikview

abc_18 — Fri, 04 Mar 2016 11:06:51 GMT

Hi,

I am getting expected output now by using cross table, But now I need to calculate one field based on condition.

I am using this calendar script in my application.

Set vFM = 10 ; // First month of fiscal year(Fiscal year starts from OCT-SEPT)

Set vFD = 6; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

MasterCalendar:

Load distinct

Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(Month, fMonth) as FMonth, // Dual fiscal month

Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,

Ceil((Date-StartOfFWeekOne+1)/7) as FWeekNo,

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,

WeekStart(FYearStart,0,$(vFD)) as StartOfFWeekOne,

Load

Date,

YearStart(Date,0,$(vFM)) as FYearStart,

Year(Date) as Year,

Month(Date) as Month,

Date(Monthstart(Date), 'MMM-YYYY') as MonthYear,

Week(Date) as ISOWeekNo,

Dual(WeekDay(Date),Mod(WeekDay(Date-$(vFD)),7)+1) as WeekDay,

Day(Date) as Day,

Date(Date, 'MM/DD') as DATEMMDD

Resident Result;

I need to write this condition in my QVW file:-

if I select year 2015,and if Division is 'PUMA', then value need to calculate based on year ie' Jan-15 to Dec-15 else for other division

calculation is based on fiscal year(Oct-14 to Sep-15).

only for PUMA division we are following this year format(Jan-15 to Dec-15).

how to implement that logic in Expression.

Re: Not getting proper values from excel in qlikview

Fri, 04 Mar 2016 18:22:39 GMT

I guess that it's bug format data....or qlikview not recognize properly. Try follow Naveen06 steps

Regards