topic Re: issue loading data in correct format in QlikView

issue loading data in correct format

Thu, 09 Apr 2015 15:02:23 GMT

Hi,

Im facing a very anoying issue while loading my data. I have a SharePoint list with several colums formated as numbers. See an example:

coulmn_1	column_2
2
	22
24
2	552
42

So in those columns can be numbers or nothing. When I load my data I want to fill the empty cells with 0, that works fine using following syntax:

LOAD

if([column_1]='',0,[column_1]);

FROM

xxxxx

BUT if I use a straight Table and try to show my Totals using "Expression Total" nothing happens .... For sure I could use "Sum of Rows" but later on I want to format the expression with money() so I need to use "Expression Total" to realize the money format in the Total row. So basicly I didn't even start with anything ... just this most simple example just doesn't work ... what is my mistake?

The weird part is, that if there is a column with only empty cells, so in fact all fields are filled with 0, the "Total Expression" row works ..... so what is that?

Because originally I wanted to import data from the list which already was formatted as a currency .... but same issues there so I tried to look for my mistake in a simple example but yeah ....

Please help me! Im almost becoming desperate.

Thanks for help!

Re: issue loading data in correct format

awhitfield — Thu, 09 Apr 2015 15:21:05 GMT

Could you post a sample qvw please?

Andy

Re: issue loading data in correct format

sunny_talwar — Thu, 09 Apr 2015 15:22:43 GMT

What happens if you try this instead of what you have right now?

LOAD

If(Len(Trim([column_1])) = 0 or IsNull([Column_1]), 0, [column_1]);

FROM

xxxxx

Best,

Sunny

Re: issue loading data in correct format

robert_mika — Thu, 09 Apr 2015 15:24:08 GMT

Is that nothing or space or no-break space in your column?

Usually when your column have only Numbers the empty cell should be ignored

For space you can use this:L

LOAD if(column_1=' ',0,column_1) as column_1

FROM

COMAPRE.xlsx

(ooxml, embedded labels, table is Sheet3);

Re: issue loading data in correct format

Thu, 09 Apr 2015 15:29:14 GMT

You don't actually need the second check there, the null would have a len of 0 also.

Can just go with below to save the extra code

If(Len(Trim([column_1])) = 0, 0, [column_1])

Joe

Re: issue loading data in correct format

Thu, 09 Apr 2015 21:09:22 GMT

Thanks for the answers. But I don't see the issue in the detection of the empty column .... filling the zeros works. I now used this method:

if(len(trim([column_1]))=0,0,[column_1]),

if(len(trim([column_12))=0,0,[column_2)

still the "Expression Total" doesn't work until that point ...

BUT I found a solution in the manual:

Expression Total

If this option is selected, the expression total will be calculated using all the values of the field. If, e.g., the selected column contains the average salaries for different business categories, choosing the Expression Total option will result in the average salary for all the business categories.

So I just did this in the Straight Table:

sum(column1)

sum(money(column_1))

So thanks anyway for your help!