How to make field name from partial load return 2320 ?

[paulyeo11]

Hi All

My load script below :-

/////////////////////////////////////////////////////////////////////////////////////////'

GL_TABLE:
LOAD
'PMC' as SOURCE,
[Account Code] as AccountCode,
// Below return correct value
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]);

// Partial Relaod For create P_Revene_
TABLE_P:
left keep (GL_TABLE)
load [AccountCode],
// BELOW IS RETURN WRONG VALUE.
if([AccountCode]>=5000000 and [AccountCode]<=5999999,'P_Revenue') as [P_Revenue_]
resident GL_TABLE;

/////////////////////////////////////////////////////////////////////////////////////////'

I get 2 different result :-

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

Below expression return correct value :-
=num(Sum(Aggr(SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=Max({<sales = {'*'}, year = {$(=Max(year))}>}month))"},P_Revenue={P_Revenue}>}Amount*-1/1000), SOURCE)),)

Below expression return wrong value :-
=num(Sum(Aggr(SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=Max({<sales = {'*'}, year = {$(=Max(year))}>}month))"},P_Revenue_={P_Revenue_}>}Amount*-1/1000), SOURCE)),)

May i know how to make P_Revenue_ also return 2330 ?

Paul Yeo

[Frank_Hartmann · Accepted solution]

partial reload means, that you add new data to your data model without reloading the existing tables/data. therefor you need to use partial reload button (File-->PartialReload)

the structure of the load has to be like this:

TABLE_P:
 Add load [AccountCode],
if([AccountCode]>=5000000 and [AccountCode]<=5999999,'P_Revenue') as [P_Revenue_]
resident GL_TABLE;

[paulyeo11 · Accepted solution]

Hi Sir

Thank you very much , after you help in give me your suggestion , i manage to find out i make one more mistake , and is i forget to place _  now work fine.

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

Paul