topic Re: Concatenate two rows to one row in QlikView

Concatenate two rows to one row

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Concatenate two rows to one row

dsharmaqv — Wed, 11 Jan 2017 08:45:58 GMT

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;

Re: Concatenate two rows to one row

ahaahaaha — Wed, 11 Jan 2017 12:38:46 GMT

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

Re: Concatenate two rows to one row

Wed, 11 Jan 2017 12:46:44 GMT

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

Re: Concatenate two rows to one row

Wed, 11 Jan 2017 12:50:28 GMT

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

Re: Concatenate two rows to one row

Anonymous — Thu, 12 Jan 2017 03:26:06 GMT

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.

Re: Concatenate two rows to one row

parimikittu — Thu, 12 Jan 2017 05:03:13 GMT

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.

Re: Concatenate two rows to one row

ganeshsvm — Thu, 12 Jan 2017 07:15:19 GMT

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

Re: Concatenate two rows to one row

ahaahaaha — Thu, 12 Jan 2017 13:49:08 GMT

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

Re: Concatenate two rows to one row

nitin_01 — Thu, 12 Jan 2017 14:57:26 GMT

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.