topic Re: How to make partial reload field [P_Revenue] Return correct result which is 2320K. in QlikView

How to make partial reload field [P_Revenue] Return correct result which is 2320K.

paulyeo11 — Mon, 14 Oct 2019 04:44:08 GMT

Hi All

I have below code that creatw 2 field name :-
[P_Revenue] This is from load script.
[P_Revenue_] This is from PArtial reload script.

GL_TABLE:
LOAD
'TDS' as SOURCE,
date#(MakeDate(@53:57T,if(@50:52T=13,12,@50:52T) ,1),'DD/MM/YYYY')as date,
@124:129T as [Reporting Code]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\FS_TDS_.TXT (ansi, fix, no labels, header is 0, record is line);

Concatenate
LOAD
'PMC' as SOURCE,
[Account Code] as AccountCode,
If([Account Code]>=5000000 and [Account Code]<=5999999,'P_Revenue') as [P_Revenue],
[Deb./Cred. (LC)] AS Amount,
Date( Date#([Posting Date], 'DD.MM.YY'), 'DD/MM/YYYY') as [date]
FROM
[C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\RAW DATA SAP\GL_PMC_2019.xlsx]
(ooxml, embedded labels, table is [Modified GL Data]);

TABLE_P:
left keep (GL_TABLE)
load [AccountCode],

// below script is not working.
if([AccountCode]>=5000000 and [AccountCode]<=5999999,'P_Revenue') as [P_Revenue_]
resident GL_TABLE;

When i apply the below expression for above 2 field :-

Money(
Sum(Aggr(SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=Max({<sales = {'*'}, year = {$(=Max(year))}>}month))"},$(ColumnDim901)={$(ColumnDim901)}>}Amount*1)/$(Columndim89)/1000, $(ColumnDim108)))
,
$(vMoneyFormatK))

I get 2 different result :-

[P_Revenue] Return correct result which is 2320K.
[P_Revenue_] Return wrong result which is 0.

May i know where go wrong ?

Paul Yeo

Re: How to make partial reload field [P_Revenue] Return correct result which is 2320K.

paulyeo11 — Mon, 14 Oct 2019 04:44:55 GMT

My QVW

Re: How to make partial reload field [P_Revenue] Return correct result which is 2320K.

paulyeo11 — Tue, 15 Oct 2019 02:05:59 GMT

Hi All

Can some one give me some hint , how to troubleshoot ?

Paul

Re: How to make partial reload field [P_Revenue] Return correct result which is 2320K.

Brett_Bleess — Mon, 21 Oct 2019 17:47:28 GMT

Paul, the first thing I would recommend is dealing with the synthetic table in your data model, as these can cause issues, here is a Design Blog post regarding that:

https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634

I think what may have folks confused is your use of the term' partial' reload, but it does not appear that is what you are doing here, as I do not see any Add or Replace items in your load statements...

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Partial%20Reload.htm

I suspect this may be was is confusing for others as well, but I will let folks have a look here based upon what I have added, but this is the best I have to try to help you sort things out.

Regards,
Brett

Re: How to make partial reload field [P_Revenue] Return correct result which is 2320K.

paulyeo11 — Mon, 21 Oct 2019 22:18:57 GMT

Hi Sir

Thank you for your sharing, I have found my issue , it is because i have forget the "_"

if([AccountCode]>=5000000 and [AccountCode]<=5999999,'P_Revenue_') as [P_Revenue_]

Now working fine.

Paul