Assigning Separate Day for Quantity and VALUE

[munnawar]

HI Everone,

i Have data for Each day as Quantity n Value in QVD , I separated data for Quantity and value using CROSS JOIN n creating separate QVD,

and loading them separately . Either if i CONCATENATE or  JOIN both QVD's , My data is Dividing Day in two Separate day for Quantity and Value

but i want in Single row with single day

for Ex

Day   QTY    value

1        10         -     

1         -         50

2        20       -

2        -         100

But I need

Day   QTY   Value

1         10      50

2          20     100

Please help me out

Thanks

[sunny_talwar]

What is the script that you use?

[trdandamudi]

One way is as below:

Data:
Load * inline [
Day,QTY,value
1,10
1, ,50
2, 20
2, ,100
];

NoConcatenate
Final:
Load Day,Sum(QTY) as QTY,
Sum(value) as value
Resident Data
Group By Day;

Drop table Data;

[munnawar]

Hi Sunny ,

The DATA in QVD is storing DAY wise for QTY and VALUE and I Sliced DATA into separate QVD using CROSSTABLE n Loading this below script

The script is like this , Yet if i introduce Dummy column for QTY & VALUE , it is showing same output .

ZMM_INVENTORY:

LOAD

Material_MATNR,

     Plant_WERKS,

     Month_MONYR,

     Day,

     num(Day,'00')&'/'&right(Month_MONYR,2)&'/'&left(Month_MONYR,4) as Date,

     QTY as InventoryQty,

Join

LOAD

Material_MATNR,

     Plant_WERKS,

     Month_MONYR,

     Day,

     num(Day,'DD')&'/'&right(Month_MONYR,2)&'/'&left(Month_MONYR,4) as Date, 

VALUE as InventoryValue

This Days like 1,1,2,2,3,3 its is repeating n giving me separate QTY & VALUE

[munnawar]

Hi Thirumala thanks for feedback,

Im not using INLINE LOAD ,Data is coming from QVD's, i showed only for reference , i am describing below plz let me know

The DATA in QVD is storing DAY wise for QTY and VALUE and I Sliced DATA into separate QVD using CROSSTABLE n Loading this below script

The script is like this , Yet if i introduce Dummy column for QTY & VALUE , it is showing same output .

ZMM_INVENTORY:

LOAD

Material_MATNR,

     Plant_WERKS,

     Month_MONYR,

     Day,

     num(Day,'00')&'/'&right(Month_MONYR,2)&'/'&left(Month_MONYR,4) as Date,

     QTY as InventoryQty,

Join

LOAD

Material_MATNR,

     Plant_WERKS,

     Month_MONYR,

     Day,

     num(Day,'DD')&'/'&right(Month_MONYR,2)&'/'&left(Month_MONYR,4) as Date,

VALUE as InventoryValue

This Days like 1,1,2,2,3,3 its is repeating n giving me separate QTY & VALUE

[cweiping]

try it.

ZMM_INVENTORY_N:

NoConcatenate

Final:

Load

  Material_MATNR,

    Plant_WERKS,

    Month_MONYR,

    Day,

    Date,

    ,Sum(InventoryQty) as InventoryQty,,

    Sum(InventoryValue) as InventoryValue

Resident ZMM_INVENTORY

Group By

    Material_MATNR,

    Plant_WERKS,

    Month_MONYR,

    Day,

    Date;

Drop table ZMM_INVENTORY;

[sunny_talwar]

What is the source of both these two loads? Is it a same file or different source?

[munnawar]

Hi Sunny,

The first Source is QVD which has data as MaterialNor,Plant,MonthYear this three coloumn  Next colomn's are Day wise data like  DAY1 Quantity,DAY1 Value,DAY2 Quantity,DAY2 Value .... so on.

This QVD i have separated for Value and Quantity into two different QVD's using CROSSTABLE and finally

Concatenate 

[sunny_talwar]

So the source for both guys are the same qvd?... would you be able to share the script you used to create the QVD (where you used CROSSTABLE and concatenate)

[stabben23]

Hi,

where do Field containing C102021B comes from? if you "delete" that Field in Your table, will you then have it on one row?