Creating two fields from a field based on its field value

[Laxbax]

Hello

I have a table where there is a "key" field with two values 'AB' or 'LA' this creates two rows for every item i have.

I want to load them as only one line, 

Original output:

Purch.Doc.	Item	CC	Deliv. Date	Quantity	Qty Red.
4500083355	00010	AB	2019-03-02	12,000	11,000
4500083355	00020	AB	2019-03-02	10,000	10,000
4500083355	00030	AB	2019-03-02	13,000	13,000
4500083355	00040	AB	2019-03-02	6,000	6,000
4500083355	00050	AB	2019-03-02	6,000	6,000
4500083355	00060	AB	2019-03-02	6,000	6,000
4500083355	00010	LA	2019-03-04	11,000	11,000
4500083355	00020	LA	2019-03-04	10,000	10,000
4500083355	00030	LA	2019-03-04	13,000	13,000
4500083355	00040	LA	2019-03-04	6,000	6,000
4500083355	00050	LA	2019-03-04	6,000	6,000
4500083355	00060	LA	2019-03-04	6,000	6,000
4500083355	00070	LA	2019-04-04	3,000	3,000
4500083355	00080	LA	2019-03-12	3,000	3,000
4500083355	00090	LA	2019-03-12	3,000	3,000
4500083355	00100	LA	2019-03-12	3,000	3,000
4500083355	00110	LA	2019-03-22	1,000	1,000

//The Load Scripts have been adjsuted so its easier to see, if there are any typos they are only on this post the script runs without error, i just dont get the expected output.

LOAD

 PO,
Item,
PO&'-'& Item as CCPOrad,
CC
"Delivery Date"
IF(CC='AB', "Delivery Date") as "AB Date",
IF(CC='LA', "Delivery Date") as "LA Date",
IF(CC='AB', Quantity) as ABQTY,
IF(CC='LA',Quantity) as LAQTY,
IF(CC='AB',"Quanitity Reduced") as ABQTYred,
IF(CC='LA',"Quanitity Reduced") as LAQTYred

EKES:

Load
PO,
Item,
CCPOrad,
"LA Date",
"AB Date",
ABQTY,
LAQTY,
ABQTYred,
LAQTYred,
ABQTY - ABQTYred as "Open ABQTY",
LAQTY - LAQTYred as "Open LAQTY"

Resident EKES;

Output after my attempts:
(Reduced to 8 Lines)

PO	Item	CCPOrad	AB Date	LA Date	Open ABQTY	Open LAQTY
4500083355	00020	4500083355-00020	2019-03-02	-	4	-
4500083355	00020	4500083355-00020	-	2019-03-04	-	4
4500083355	00030	4500083355-00030	2019-03-02	-	4	-
4500083355	00030	4500083355-00030	-	2019-03-04	-	4
4500083355	00040	4500083355-00040	2019-03-02	-	4	-
4500083355	00040	4500083355-00040	-	2019-03-04	-	4
4500083355	00060	4500083355-00060	2019-03-02	-	4	-
4500083355	00060	4500083355-00060	-	2019-03-04	-	4

Desired Output:

PO	Item	CCPOrad	AB Date	LA Date	Open ABQTY	Open LAQTY
4500083355	00020	4500083355-00020	2019-03-02	2019-03-04	4	4
4500083355	00030	4500083355-00030	2019-03-02	2019-03-04	4	4
4500083355	00040	4500083355-00040	2019-03-02	2019-03-04	4	4
4500083355	00060	4500083355-00060	2019-03-02	2019-03-04	4	4

I'm probably going about this the wrong way and I could use some help.
Let me know if you need any more information or actual QVD data, and I'll see what I can do.

[agigliotti]

could you send us the qvd files to look at?

[lironbaram · Accepted solution]

hi 

have you should try this ,

EKES:

Load
PO,
Item,
CCPOrad,
max("LA Date") as "LA Date",
max("AB Date") as "AB Date",
sum(ABQTY)        as ABQTY,
sum(LAQTY)        as LAQTY,
sum(ABQTYred)        as ABQTYred,
sum(LAQTYred)        as LAQTYred,
sum(ABQTY - ABQTYred) as "Open ABQTY",
sum(LAQTY - LAQTYred) as "Open LAQTY"

Resident EKES

group by PO,
Item,
CCPOrad;

[Laxbax]

Thanks, This did the trick.

I was trying to get this to work before but I had no experience with group by clause in resident.

+100