topic SAP Tables Join and concatenate together in QlikView

SAP Tables Join and concatenate together

Fri, 24 Sep 2010 14:20:18 GMT

Hi all,

May be I am doing not right. correct me If I am wrong.


VBAK:
Load ,
VBELN,
VBELN as SALESDOC,
...,
.....from VBAK.qvd;
VBAP_temp:
Load
VBELN,
....,
.....from VBAP.qvd
where exists (VBELN, VBELN);
VBAP:
Load
1 as reason,
VBELN,
VBELN as SALESDOC,
VBELN/POS as SALESDOCITEM,
......
......
Resident VBAP_temp
where GRUND>0;
concatenate
Load
VBELN,
VBELN as SALESDOC,
VBELN/POS as SALESDOCITEM,
2 as reason,
......
......
Resident VBAP_temp
where GRUND<0;
Drop Table VBAP_temp;

Actually I want VBAK Inner Join VBAP, I think I cannot do it here?

Any other way to do it keeping optimized load in tact!

Thanks in Advance

sravan

SAP Tables Join and concatenate together

pover — Fri, 24 Sep 2010 15:46:52 GMT

sraven,

Like you said it should be at least VBAK left join VBAP. Concatenate will not work. If you're worried about the load speed why don't you keep the tables separate? In that case you can use the function 'keep' to make sure there is no header in VBAK without line positions in VBAP. Or why don't you want to do a join?

Regards.

SAP Tables Join and concatenate together

Fri, 24 Sep 2010 15:59:10 GMT

Hi Karl,

The problem is that I have a lot of tables in the Data model and I am compressing to reduce the overall data and in this process reducing the tables with the help of Joins. Keep is a good advice ok, that I can do but I think still I have two tables. Innerjoin to make work I have to load again the same table like this

Load * resident VBAK

InnerJoin

Load* resident VBAP

innerjoin

Load * resident VBEP

etc..

As you see I am loading tables 2 times. I think this can be done in another way with out loading the same table again. Left Keep again will not merge. I need here Join to merge.

may be someone can help

Thanks

Sravan

SAP Tables Join and concatenate together

pover — Fri, 24 Sep 2010 16:15:41 GMT

Hi Sravan,

To do the join you don't need to load the table 2 times. The condition "where exists (VBELN, VBELN);" is not necessary since the inner join will automatically verifty that no VBELN that doesn't exist in the 2 tables is in the final table. I assume that you are using VBELN as you key, correct?

If you have the table,

VBAK:
VBELN | KUNNR
1 | 2341
2 | 3928
3 | 2928

inner join

VBAP:
VBELN | POSNR
1 | 1
1 | 2
2 | 1

your final result without doing the exists is:

VBELN | KUNNR | POSNR
1 | 2341 | 1
1 | 2341 | 2
2 | 3928 | 1

SAP Tables Join and concatenate together

Fri, 24 Sep 2010 17:08:21 GMT

Hey Karl,

Thanks again. You are absolutely right but what my problem is, I think you are not getting.

Normal 0 21 false false false DE X-NONE X-NONE MicrosoftInternetExplorer4

VBAK:
VBELN | KUNNR
1 | 2341
2 | 3928
3 | 2928

inner Join

VBAP: But here in VBAP I need to concatenate two tables into one because of ABGRU>0 and ABGRU<0

VBAP:

Load * from VBAP where ABGRU>0(Flag1);

concatenate Load * from VBAP where ABGRU<0(Flag2);

VBELN|POSNR|ABGRU|Flag

1|1|01|1

1|2||2

2|1|02|1

[/ncode]

And Here innerjoin does not work because I am concatenating. Thats why I loaded VBAP_temp(More than 4 million records) reduced it with where exists and then concatenated(400 Thousand records) to reduce the Data.

If I need to innerjoin VBAK to VBAP then I must again reload the Data from VBAK and then Innerjoin VBAP.

Can you recommed otherway here??

Thanks for your answer. Hope I am clear now.

SAP Tables Join and concatenate together

pover — Fri, 24 Sep 2010 17:47:44 GMT

I agree. So I would suggest creating the additional columns (if(GRUND>0, 1, 2) as reason, salesdocitem, etc.), logic and concatenate when creating the QVD where you won't see much impact in the reload time since it is slower. Then just do the inner join in the this QVW.

Regards.

SAP Tables Join and concatenate together

Fri, 24 Sep 2010 17:58:06 GMT

Hey karl,

Do you mean loading second time after concatenating and Innerjoin with VBAK. That I already did. I am seeing for a better solution. Anyway thanks.

SAP Tables Join and concatenate together

pover — Fri, 24 Sep 2010 18:14:40 GMT

Sraven,

No. I'm saying that you can add the necessary data to VBAP when you pull the information from SAP and create a VBAP.QVD that doesn't have to be concatenated before the inner join, but maybe that's not a good idea after all.

Why are you concatenating VBAP with itself when you can create a column like this:

if(GRUND<0,2,1) as Reason

Tell me if I'm still missing some detail.