Home

Please help me get the output for the given file

%3CLINGO-SUB%20id%3D%22lingo-sub-856987%22%20slang%3D%22en-US%22%3EPlease%20help%20me%20get%20the%20output%20for%20the%20given%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856987%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%3C%2FP%3E%3CP%3EI've%20given%20layout%20of%20an%20excel%20file%20below%20which%20has%20three%20sheets%20out%20of%20which%20%22invoice%22%20and%20%22packaging%22%20sheets%20are%20inputs%20whilst%20the%20%22output%22%20sheet%20contains%20the%20desired%20output%20I%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20finding%20it%20difficult%20to%20frame%20an%20query%20for%20the%20same%20-%20It'll%20be%20great%20if%20someone%20can%20help%20me%20on%20that.%3C%2FP%3E%3CP%3EThanks%20in%20Advance.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPACKING%20SHEET-%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EPacking%20List%20No%3C%2FTD%3E%3CTD%3EUnique%20ID%3C%2FTD%3E%3CTD%3EPacking%20Date%3C%2FTD%3E%3CTD%3ECustomer%20Name%3C%2FTD%3E%3CTD%3EItem%20Name%3C%2FTD%3E%3CTD%3EQTY%3C%2FTD%3E%3CTD%3ERate%3C%2FTD%3E%3CTD%3EValue%3C%2FTD%3E%3CTD%3ESO%20No%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1x%3C%2FTD%3E%3CTD%3E01-04-19%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1y%3C%2FTD%3E%3CTD%3E01-04-19%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3Ey%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E1200%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E2z%3C%2FTD%3E%3CTD%3E01-06-19%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E1500%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E2z%3C%2FTD%3E%3CTD%3E01-08-19%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E1x%3C%2FTD%3E%3CTD%3E01-08-19%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E1y%3C%2FTD%3E%3CTD%3E01-08-19%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3Ey%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E1800%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E3z%3C%2FTD%3E%3CTD%3E15-09-19%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E800%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E3z%3C%2FTD%3E%3CTD%3E30-09-19%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E2000%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E4a%3C%2FTD%3E%3CTD%3E17-09-19%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E2100%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E4a%3C%2FTD%3E%3CTD%3E29-09-19%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E2100%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EINVOICE%20SHEET-%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EInvoice%20No%3C%2FTD%3E%3CTD%3EUnique%20ID%3C%2FTD%3E%3CTD%3EInvoice%20Date%3C%2FTD%3E%3CTD%3ECustomer%20Name%3C%2FTD%3E%3CTD%3EItem%20Name%3C%2FTD%3E%3CTD%3EQTY%3C%2FTD%3E%3CTD%3ERate%3C%2FTD%3E%3CTD%3EValue%3C%2FTD%3E%3CTD%3ESO%20No%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1x%3C%2FTD%3E%3CTD%3E01-05-19%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E80%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E240%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1y%3C%2FTD%3E%3CTD%3E01-05-19%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3Ey%3C%2FTD%3E%3CTD%3E150%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E900%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2z%3C%2FTD%3E%3CTD%3E01-07-19%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E1000%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E1x%3C%2FTD%3E%3CTD%3E01-09-19%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E1y%3C%2FTD%3E%3CTD%3E01-09-19%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3Ey%3C%2FTD%3E%3CTD%3E350%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E2100%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E2z%3C%2FTD%3E%3CTD%3E01-09-19%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E150%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E750%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E3z%3C%2FTD%3E%3CTD%3E15-09-19%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E180%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E720%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E3z%3C%2FTD%3E%3CTD%3E01-10-19%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E495%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E1980%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E4a%3C%2FTD%3E%3CTD%3E30-09-19%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3CTD%3E600%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E4200%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDESIRED%20OUTPUT-%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ESO%20No%3C%2FTD%3E%3CTD%3EInvoice%20No%3C%2FTD%3E%3CTD%3EPacking%20List%20No%3C%2FTD%3E%3CTD%3EItem%20Name%3C%2FTD%3E%3CTD%3EInvoice%20Date%3C%2FTD%3E%3CTD%3EPacking%20Date%3C%2FTD%3E%3CTD%3EInvoiced%20Quantity%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E01-05-19%3C%2FTD%3E%3CTD%3E01-04-19%3C%2FTD%3E%3CTD%3E80%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E01-09-19%3C%2FTD%3E%3CTD%3E01-08-19%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ex%3C%2FTD%3E%3CTD%3E01-09-19%3C%2FTD%3E%3CTD%3E01-08-19%3C%2FTD%3E%3CTD%3E80%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Ey%3C%2FTD%3E%3CTD%3E01-05-19%3C%2FTD%3E%3CTD%3E01-04-19%3C%2FTD%3E%3CTD%3E150%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Ey%3C%2FTD%3E%3CTD%3E01-09-19%3C%2FTD%3E%3CTD%3E01-08-19%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3Ey%3C%2FTD%3E%3CTD%3E01-09-19%3C%2FTD%3E%3CTD%3E01-08-19%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E01-07-19%3C%2FTD%3E%3CTD%3E01-06-19%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E01-09-19%3C%2FTD%3E%3CTD%3E01-06-19%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E01-09-19%3C%2FTD%3E%3CTD%3E01-08-19%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E15-09-19%3C%2FTD%3E%3CTD%3E15-09-19%3C%2FTD%3E%3CTD%3E180%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3Ez%3C%2FTD%3E%3CTD%3E01-10-19%3C%2FTD%3E%3CTD%3E15-09-19%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3EZ%3C%2FTD%3E%3CTD%3E01-10-19%3C%2FTD%3E%3CTD%3E30-09-19%3C%2FTD%3E%3CTD%3E475%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3CTD%3E30-09-19%3C%2FTD%3E%3CTD%3E17-09-19%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3CTD%3E30-09-19%3C%2FTD%3E%3CTD%3E29-09-19%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMETADATA-%20SO%20is%20Sales%20Order%20%7C%20The%20output%20is%20based%20on%20FIFO%20Logic%20%7C%20Packaging%20stage%20is%20primary%20whilst%20Invoice%20stage%20is%20secondary%3C%2FP%3E%3C%2FLINGO-BODY%3E
dataleadgein
New Contributor

Hi guys

I've given layout of an excel file below which has three sheets out of which "invoice" and "packaging" sheets are inputs whilst the "output" sheet contains the desired output I want.

 

I'm finding it difficult to frame an query for the same - It'll be great if someone can help me on that.

Thanks in Advance. 

 

PACKING SHEET-

Packing List NoUnique IDPacking DateCustomer NameItem NameQTYRateValueSO No
11x01-04-19Ax10033001
21y01-04-19Ay200612001
32z01-06-19Bz300515002
42z01-08-19Bz10055002
51x01-08-19Ax10033001
61y01-08-19Ay300618001
73z15-09-19Bz20048003
83z30-09-19Bz500420003
94a17-09-19Ca300721004
104a29-09-19Ca300721004

 

INVOICE SHEET-

Invoice NoUnique IDInvoice DateCustomer NameItem NameQTYRateValueSO No
11x01-05-19Ax8032401
11y01-05-19Ay15069001
22z01-07-19Bz200510002
31x01-09-19Ax10033001
41y01-09-19Ay350621001
52z01-09-19Bz15057502
63z15-09-19Bz18047203
73z01-10-19Bz495419803
84a30-09-19Ca600742004

 

DESIRED OUTPUT-

SO NoInvoice NoPacking List NoItem NameInvoice DatePacking DateInvoiced Quantity
111x01-05-1901-04-1980
111x01-09-1901-08-1920
111x01-09-1901-08-1980
112y01-05-1901-04-19150
142y01-09-1901-08-1950
146y01-09-1901-08-19300
223z01-07-1901-06-19200
253z01-09-1901-06-19100
254z01-09-1901-08-1950
367z15-09-1915-09-19180
377z01-10-1915-09-1920
378Z01-10-1930-09-19475
489a30-09-1917-09-19300
4810a30-09-1929-09-19300

 

METADATA- SO is Sales Order | The output is based on FIFO Logic | Packaging stage is primary whilst Invoice stage is secondary

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies