Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
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
];
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;
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.
But, As per your output we get same
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.
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.
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)
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
HA HA!
You saying that structure should be the same OPENED MY EYES.
Thank you!!!