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: 
paulyeo11
Master
Master

How to make field name from partial load return 2320 ?

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

2 Solutions

Accepted Solutions
Frank_Hartmann
Master II
Master II

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;

 

View solution in original post

paulyeo11
Master
Master
Author

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

View solution in original post

2 Replies
Frank_Hartmann
Master II
Master II

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
Master
Master
Author

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