Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am loading two set of data
Table 1: Some list of Invoice and their Purchase Order (PO) number
Table 2: A Purchase Order Record Master
Table 1 is having limited (few hundred thousand) data but Table 2 as the name suggest is has massive data (more than few millions)
So, when essentially, I am loading millions of excess record from Table 2 which is not reverent to me and just unnecessarily occupy space and make further computation complex.
So Is there any way I can do limit the load from Table 2 to the PO's which are listed in Table 1?
Any help will be appreciated as I am going mad in finding some solution in last couple of days.
Thanks,
Oh ok. so may be try this:
Table1:
LOAD InvoiceNum,
@1 as [Invoice Number]
@2 as [Purchase Order Number]
.....
FROM Tablename1;
Table2:
LOAD *
WHERE Exists([Purchase Order Number]);
LOAD PurchaseOrderRecordMaster,
@1 as [Purchase Order Number]
@2 as [Plant Name]
.....
FROM Tablename2;
Hi,
If I understand you right, you should do the following:
First load Table 1
Table1:
LOAD
PO,
Field2,
Field3
FROM source
Then LOAD Table2 with a where clause:
Table2:
LOAD
PO,
Field2,
Field3
FROM source where exists (PO);
This way table 2 will only load the PO's that are previously loaded (in Table1)
May be this? Change the field names accordingly in your 2nd table where the fieldname in your table1 should have the values in fieldname of table2. I assumed and listed the field names in WHERE Exists condition. Hope you got it.
Table1:
LOAD InvoiceNum,
PurchaseOrderNumber
.....
FROM Tablename1;
Table2:
LOAD PurchaseOrderRecordMaster,
.....
FROM Tablename2
WHERE Exists(PurchaseOrderNumber, PurchaseOrderRecordMaster);
Thank you Vishwarath!
But I have used manual naming of column so I think its not working form me.
So can you please provide solution of below
Table1:
LOAD InvoiceNum,
@1 as [Invoice Number]
@2 as [Purchase Order Number]
.....
FROM Tablename1;
Table2:
LOAD PurchaseOrderRecordMaster,
@1 as [Purchase Order Number]
@2 as [Plant Name]
.....
FROM Tablename2
WHERE Exists(???, ???);
So what should I put where I have used "??" should it be the as name or the Column name (I.e. "@2")
Oh ok. so may be try this:
Table1:
LOAD InvoiceNum,
@1 as [Invoice Number]
@2 as [Purchase Order Number]
.....
FROM Tablename1;
Table2:
LOAD *
WHERE Exists([Purchase Order Number]);
LOAD PurchaseOrderRecordMaster,
@1 as [Purchase Order Number]
@2 as [Plant Name]
.....
FROM Tablename2;
Hi,
Please check this.
Table1:
LOAD * INLINE [
InvoiceID,Purchase Order,Value1
1,PO1,1
2,PO2,2
3,PO3,3
4,PO4,4
5,PO5,5
6,PO6,6
7,PO7,7
8,PO8,8
9,PO9,9
10,PO10,10
11,PO11,11
12,PO12,12
13,PO13,13];
Table2:
LOAD * INLINE [
Purchase Order,Date,Values2
PO1,1-Jan-18,1
PO2,2-Jan-18,2
PO3,3-Jan-18,3
PO4,4-Jan-18,4
PO5,5-Jan-18,5
PO6,6-Jan-18,6
PO7,7-Jan-18,7
PO8,8-Jan-18,8
PO9,9-Jan-18,9
PO10,10-Jan-18,10
PO11,11-Jan-18,11
PO12,12-Jan-18,12
PO13,13-Jan-18,13
PO14,14-Jan-18,1
PO15,15-Jan-18,2
PO16,16-Jan-18,3
PO17,17-Jan-18,4
PO18,18-Jan-18,5
PO19,19-Jan-18,6
PO20,20-Jan-18,7
PO21,21-Jan-18,8
PO22,22-Jan-18,9
PO23,23-Jan-18,10
PO24,24-Jan-18,11
PO25,25-Jan-18,12
PO26,26-Jan-18,13
PO27,27-Jan-18,11
PO28,28-Jan-18,12
PO29,29-Jan-18,13
PO30,30-Jan-18,14]
where Exists ([Purchase Order]);
Thank you Vishwarath!!. Its working perfectly.