Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;