topic Re: what join is most suitable for my data model ? in QlikView

what join is most suitable for my data model ?

paulyeo11 — Wed, 15 May 2013 02:54:42 GMT

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

Re: what join is most suitable for my data model ?

Wed, 15 May 2013 05:06:37 GMT

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.

Re: what join is most suitable for my data model ?

er_mohit — Wed, 15 May 2013 05:17:35 GMT

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

Re: what join is most suitable for my data model ?

paulyeo11 — Wed, 15 May 2013 10:44:04 GMT

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