Concatenate two rows to one row

[#1]

Hi!

I am looking for some ideas how to solve the following issue:

The table looks like this at the moment:

Where I am struggling at the moment is to get both line into one line --> as you can see, the number in VLENR.LTAP and NLENR.LTAP is the same - so this is the "key" for combining both lines. And I need two more columns where the BDATU.LTAK and BZEIT.LTAK of the second line is displayed.

So the table should be as followed:

I need it this way to be able to calculate the duration (time) of this process.

Thank you a lot for your help!

Regards,

Barbara

[dsharmaqv]

you should try like this

Concat(Comment,' ') as NewComment

use this with resident load

example

load
Concat(Comment,' ') as NewComment
resident Data
Group by Date;

[ahaahaaha]

Hi Barbara,

Two questions.

1. Is it always the result table row is obtained from the two rows of the source table or the rows of the source table can be more than two?

2. Which fields in the source table is determined that the rows moving in one row of the result table?

Regards,

Adrey

[#4]

Hi Andrey!

1)  there are always two lines in the source table because I am only interested in two specific material movements (first: location 901 to 006 and second: location 006 to 921)

2) VLENR.LTAP and NLENR.LTAP determine which two rows belong toegether - both columns have the same number.. maybe it's possible combining these two fields --> VLENR.LTAP &'/'& NLENR.LTAP...
I tried this in the dimensions of the table, but there it was not appropriate.. this should happen in the script part, when loading the table.

Thank you!

Regards,

Barbara

[#5]

Hi Deepak!

Thank you for your idea - I saw this one in another post already... But I am not sure if it is the same data basis as in my example..

This is the actual loading statement, when loading the table:

LTAP:

LOAD

* ,

TANUM.LTAP as %TANUM

FROM $(Data)\LTAP.QVD (qvd);

so you would include your statement part in there?

Regards,

Barbara

[Anonymous]

Hi Barbara

I think what you're looking for is called  "slowly changing dimension".

I'm no expert, so i wont be able to give you an example off the top of my head.

hope this will get you on track however.

Thanks

Bon.

[parimikittu]

Hi, Create two tables from the same QVD. First one with where isnull(VLENR.LTAP) and second one with isnull(NLENR.LTAP). Then rename the fields accordingly and join two tables based on the common data which NLENR.LTAP or VLENR.LTAP.

[ganeshsvm]

Hi Barbara,

I just tried a work around with two rows in a table of data from the image you provided, hope this helps you.

SAPDATA:
LOAD * Inline [
MATNR, TANUM, LGORP, BDATU, BZEIT, VLTYP, VLENR, NLTYP, NLENR, NLBER, VLBER, VSOLM, VISTM, %VON, %NACH
31-110, 0005883680, 006, 04-02-2015, 22:41:15, 901,, 006, 00000000000210183625, 001, 001, 18000, 18000, 901, 006
31-110, 0005892344, 006, 06-02-2015, 11:05:53, 006, 00000000000210183625, 921,, 001, 001, 18000, 18000, 006, 921
];

QUALIFY *;
UNQUALIFY %KEY,MATNR,LGORP;
T1:
LOAD
VLENR as %KEY,
MATNR ,
LGORP,
BDATU,
BZEIT,
%VON,
%NACH
Resident SAPDATA
Where Len(Trim(VLENR))<>0;

Join 
T2:
LOAD
NLENR as %KEY,
MATNR,
LGORP,
BDATU,
BZEIT,
%VON,
%NACH
Resident SAPDATA
Where Len(Trim(NLENR))<>0;

UNQUALIFY*;
FINAL: 
LOAD %KEY,
MATNR,
LGORP,
T1.BDATU,
T2.BDATU,
T1.BZEIT,
T2.BZEIT,
T2.%VON&'/'&T1.%VON AS %VON,
T2.%NACH&'/'&T1.%NACH AS %NACH
Resident T1;

DROP Table SAPDATA,T1;

Regards,

Ganesh

[ahaahaaha]

Hi Barbara,

If I understand you correctly, the solution may be as follows.

Source data (marked with yellow lines that should be loaded and convert to a single line)

First, load the whole table.

//First, load the whole table

Table1:

LOAD MATNR.LTAP,

     TANUM.I,

     LGORT.LTAP,

     BDATU.LTAK,

     BZEIT.I.TAK,

     VLTYP.LTAP,

     VLENR.LTAP,

     NLENR.LTAP

FROM

Get

Overload the table, selecting only the rows and ordering on the basis of identical lines

NoConcatenate

Table2:

LOAD*,

If(Peek('VLENR.LTAP') <> VLENR.LTAP Or RowNo() = 1, 0, 1) as Flag //0 - new item 1 - the previous item

Resident Table1

Where (LGORT.LTAP = '921' And VLTYP.LTAP = '006') Or (LGORT.LTAP = '006' And VLTYP.LTAP = '901') //We select only the rows by values LGORT.LTAP and VLTYP.LTAP

Order by VLENR.LTAP, NLENR.LTAP; //Sort the table on the basis of identical lines

Drop Table Table1;

Get

Once overload table, reading only lines with Flag=1 and adding the desired values of the previous  rows (Flag=0)

NoConcatenate

Table3:

LOAD

MATNR.LTAP,

Previous(LGORT.LTAP) as LGORT.LTAP,

Previous(BDATU.LTAK) as BDATU.LTAK_1,

Previous(BZEIT.I.TAK) as BZEIT.I.TAK_1,

BDATU.LTAK as BDATU.LTAK_2,

BZEIT.I.TAK as BZEIT.I.TAK_2,

Previous(VLTYP.LTAP) & '/' & Previous(LGORT.LTAP) as from_to_1,

VLTYP.LTAP & '/' & LGORT.LTAP as from_to_2

Resident Table2

Where Flag = 1

Order by VLENR.LTAP, NLENR.LTAP;

DROP Table Table2;

Get

Perhaps your source data is organized a little differently. I hope that the detailed description will help you to solve your problem.

An example is shown in the attached file.

Regards,

Andrey

[nitin_01]

Hi Barbara,

This too might help,

1:
LOAD *,
     If(MATNR=Peek(MATNR) and VLENR<>Peek(VLENR) and NLENR<>Peek(NLENR),Peek(Key1),VLENR&NLENR) as Key1;
LOAD * Inline [
MATNR, TANUM, LGORP, BDATU, BZEIT, VLTYP, VLENR, NLTYP, NLENR, NLBER, VLBER, VSOLM, VISTM, %VON, %NACH
31-110, 0005883680, 006, 04-02-2015, 22:41:15, 901,, 006, 00000000000210183625, 001, 001, 18000, 18000, 901, 006
31-110, 0005892344, 006, 06-02-2015, 11:05:53, 006, 00000000000210183625, 921,, 001, 001, 18000, 18000, 006, 921
31-111, 0005883681, 006, 04-02-2015, 22:41:15, 901,, 006, 00000000000210183626, 001, 001, 18000, 18000, 901, 006
31-111, 0005892345, 006, 06-02-2015, 11:05:53, 006, 00000000000210183626, 921,, 001, 001, 18000, 18000, 006, 921
31-112, 0005883682, 006, 04-02-2015, 22:41:15, 901,, 006, 00000000000210183627, 001, 001, 18000, 18000, 901, 006
31-112, 0005892346, 006, 06-02-2015, 11:05:53, 006, 00000000000210183627, 921,, 001, 001, 18000, 18000, 006, 921
];

2:
LOAD *,
     If(MATNR=Peek(MATNR) and BDATU<>0,Peek(BDATU2),BDATU) as BDATU2,
     If(MATNR=Peek(MATNR) and BZEIT<>0,Peek(BZEIT),BZEIT) as BZEIT2,
     If(MATNR=Peek(MATNR) and %VON<>0,%VON&'/'&Peek(%VON2),%VON) as %VON2,
     If(MATNR=Peek(MATNR) and %NACH<>0,%NACH&'/'&Peek(%NACH2),%NACH) as %NACH2,
     '1' as junk
Resident 1 Order By  MATNR asc,BDATU desc,%VON desc  ;

3:
LOAD *,
     '1' as junk1
Resident 2 Where NLENR<>'' ;

DROP Table 1;
DROP Field junk From 2;
DROP Table 2;

Regards,

Nitin.