CrossTable load showing wrong results in chart expression

[Eshwar1]

Hi All,

I am trying to load one table which having some fields like below

Temp_Table:

Load

Staff_ID,

Branch_name,

Division_name,

Current Account,

Multi-Current Account,

Saving Account,

term deposit

From Table1;

this table load statement giving correct results in Table chart ex: Sum(Current Account)

due to some requirement in front end  i am doing CrossTable load to Transpose the columns to rows like below

Category_Table:

CrossTable(Countfield,CountValue,3)

Load

Staff_ID,

Branch_name,

Division_name,

Current Account,

Multi-Current Account,

Saving Account,

Term deposit

Resident Temp_Table;

Fianl:

Load *,
If(Match(Countfield,'Current Account','Multi-Current Account',),'Current',
If(Match(Countfield,'Saving Account,'),'Secured',
If(Match(Countfield,'Term deposit'),'Deposit'))) As Categories

Resident Category_Table;

Drop Category_Table

after this load statement the use same expression like above giving wrong results Sum({<Countfield={'Current Account'}>}CountValue)

how to resolve this issue, can you kindly suggest.

Thank You

Eshwar

[marcus_sommer]

Just take a look on the field-values itself within a table-box. A chart with calculations isn't suitable to find reasons behind unexpected results. Very helpful is also to include recno() and rowno() within the loads to show any duplicates in the data-set as well as being able to track source- and target-records.

Comparing both approaches against each other in this way should show the differences and hinting to the causes. As reason in your case I could imagine a different interpretation of the values. Qlik uses no data-types else an interpretation as numbers, strings or a mix of them in regard to the interpretation-variables and the order in which the field-values are loaded. This may result that some or all values are now strings and not numbers and/or that thousands/decimal-delimiters aren't wrongly interpreted or similar stuff.

In the most scenarios this worked smoothly and if not there are various resolving-approaches which will  be depending on the data. Very common is to apply an own interpretation with (max.) something like this:

num(num#(text(MyField), 'FormatPattern'), 'FormatPattern') as MyField

Another way would be to pre-load the wanted interpretation like:

Dummy: load 1.11 as MyField autogenerate 1;

t: load * from MySource;

drop tables Dummy;