creating a new table

[Anonymous]

Hello 🙂

I have two tables with invoice data:

Invoice:

LOAD

  IV,

  PART, //PAYPART

QUANT as PAYQUANT

InvoiceDetailed:

LOAD

    IV,

    PART, //ACTUALPART

    QUANT as ACTUALQUANT

for example:

Invoice table:

IV  PART    PAYQUANT

19    XXX   15

19    ZZZ    13

InvoiceDetailed table:

IV    PART    ACTUALQUANT

19    XXX    15

19    YYY    22

What I need is to have a final table with all Possibilities:

IV    PART    PAYQUANT    ACTUALQUANT

19     XXX     15                    15

19     ZZZ      13                     0

19     YYY      0                      22

I would appreiciate your help in finding a solution.

[Anil_Babu_Samineni · Accepted solution]

What is the tabular you are getting by using Join?

[Invoice table]:

LOAD * Inline [

IV,  PART   , PAYQUANT

19,    XXX ,  15

19,    ZZZ,    13

];

Join

[InvoiceDetailed table]:

LOAD * Inline [

IV ,   PART  ,  ACTUALQUANT

19  ,  XXX ,   15

19   , YYY,    22

];

[Anonymous]

Try something like

Invoice:

Load * inline [

IV,PART,PAYQUANT

19,XXX,15

19,ZZZ,13

];

InvoiceDetailedtable:

Load * inline [

IV,PART,ACTUALQUANT

19,XXX,15

19,YYY,22];

join(Invoice)

load * Resident InvoiceDetailedtable;

drop table InvoiceDetailedtable;

[Anonymous]

you can see that i have two keys - IV and APRT.

Therefore join does not work.

I have also made table of keys, and it didn't work as well since there are different shows in table invoice and table  InvoiceDetailed, So the result os always only the parts from invoice.

[Anil_Babu_Samineni]

But, As per your output we get same

[Anonymous]

If I understood correct, you are offering me to make the table using inline.

However, this is data that is being loaded from an external data base.

So inline is not an option.

[Anonymous]

Hi Ronit,

Either inline or pulled from database,, the join works the same.

Try loading the data as you are doing in the script and use

join(Invoice)

load * resident InvoiceDetailed;

and drop the table InvoiceDetailed;

you should be getting your output.

[Anonymous]

The result I am getting is:

IV    PART    PAYQUANT    ACTUALQUANT

19     XXX     15                    15

19     ZZZ      13                     0

it doesn't show me :

19     YYY      0                      22

(I believe that because part YYY only appreas in table InvoiceDetailed)

[Anil_Babu_Samineni]

If structure is same it should work. Inline just accepted the memory. it will work even external source data. Would you provide sample data in excel then let us know output. Data should real data

[Anonymous]

HA HA!

You saying that structure should be the same OPENED MY EYES.

Thank you!!!