Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Eshwar1
Contributor III
Contributor III

CrossTable load showing wrong results in chart expression

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

 

Labels (4)
1 Reply
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;