Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
when i use left keep , it create extra table, make my data model look complication and messy :-
left keep (GL_TABLE)
load [Reporting Code],
If([Reporting Code]>=1 and [Reporting Code]<=11,'1 REVENUE',
If([Reporting Code]>=20 and [Reporting Code]<=49,'2 COGS',
If([Reporting Code]>=50 and [Reporting Code]<=99,'3 OTH INCOME',
If([Reporting Code]>=100 and [Reporting Code]<=374,'4 Exp',
If([Reporting Code]>=400 and [Reporting Code]<=401,'5 TAX ',
))))) as [NAME_PNL]
resident GL_TABLE;
when i use left join , it look neat , but i get memory error.
left join (GL_TABLE)
If([Reporting Code]>=1 and [Reporting Code]<=11,'1 REVENUE',
If([Reporting Code]>=20 and [Reporting Code]<=49,'2 COGS',
If([Reporting Code]>=50 and [Reporting Code]<=99,'3 OTH INCOME',
If([Reporting Code]>=100 and [Reporting Code]<=374,'4 Exp',
If([Reporting Code]>=400 and [Reporting Code]<=401,'5 TAX ',
))))) as [NAME_PNL]
resident GL_TABLE;
Any alternative ?
Paul
I think you can use left keep.
since with left keep extra table has made,then for this,
example--
tab1:
load *
from tab1;
left keep
tab2:
load *
from tab2;
combinedtable:
load * resident tab1;
load * resident tab2;
hope this helps you.
if using left keep it create extra table, make my data model look complication and messy
then try to use concatenate but use of that it append the rows if duplicate records there
and try to use rangeonly function before if statement
load [Reporting Code],
rangeonly( If([Reporting Code]>=1 and [Reporting Code]<=11,'1 REVENUE',
rangeonly(If([Reporting Code]>=20 and [Reporting Code]<=49,'2 COGS',
rangeonly(If([Reporting Code]>=50 and [Reporting Code]<=99,'3 OTH INCOME',
rangeonly(If([Reporting Code]>=100 and [Reporting Code]<=374,'4 Exp',
rangeonly(If([Reporting Code]>=400 and [Reporting Code]<=401,'5 TAX ',
))))) )))))as [NAME_PNL]
hope it helps
Hi All
Thank you very much for both your help.
I keep reading the reply, it look very simple , when i want to start , but don't know how to start.
Hope you can advise me.
Paul