Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all!
I have some problem, but have not experience to resolve it
There are 3 tables. 1st and 2nd is ok, but 3rd need transformations. Here is simplefied scheme:
'Shipment' has only records where %ID exists in 'Agreement'.
The rule of 'Shipment's transformation:
Could you give me any idea? I think the solution is simple and beautiful ))
Regards
Sergei
[Agreement]:
LOAD * INLINE [
%ID, #Ag_Q
ID1, 100
ID2, 200
ID3, 250
];
[ShipAgreement]:
LOAD * INLINE [
%ID, %ShipAgrmID, #Sh_Q
ID2, A, 50
ID2, B, 40
];
[Shipment]:
LOAD * INLINE [
%ID, %ShipAgrmID, #Qty
ID1, , 10
ID2, A, 11
ID2, A, 12
ID3, C, 13
];
hello
when loading 3rd table, add a where clause
where isnull(%ShipAgrmID) or exists(%ShipAgrmID)
assuming you loaded the other tables first
oups !!!!
it is not(exists())
sorry
Hi,
here is one approach (I don't have all the info like the key between the tables and table you want to keep)
[Agreement]:
LOAD * INLINE [
%ID, #Ag_Q
ID1, 100
ID2, 200
ID3, 250
];
left keep([Agreement]) //'Shipment' has only records where %ID exists in 'Agreement'.
[Shipment_1]:
LOAD * INLINE [
%ID, %ShipAgrmID, #Qty
ID1, , 10
ID2, A, 10
ID2, A, 10
ID3, C, 10
];
DROP Table Agreement;
[ShipAgreement]:
LOAD AutoNumber(%ID,%ShipAgrmID) as key, * INLINE [
%ID, %ShipAgrmID, #Sh_Q
ID1, A, 50
ID2, B, 40
];
Shipment:
LOAD AutoNumber(%ID,%ShipAgrmID) as key,*
Resident Shipment_1
Where len(trim(%ShipAgrmID))=0 or Exists(%ShipAgrmID); //this line is where I put the last two conditions you have
drop Table Shipment_1;
DROP Field %ID from Shipment;
DROP Field %ShipAgrmID From Shipment;
Rename %ShipAgrmID in table two and do load of table three with a where exists on that new field name. After you can change the field name back to %ShipAgrmID
Do you meen:
[Agreement]:
... //load
[ShipAgreement]:
... //load
[Shipment]:
LOAD * INLINE [
%ID, %ShipAgrmID, #Qty
ID1, , 10
ID2, A, 11
ID2, A, 12
ID3, C, 10
]
where isNull (%ShipAgrmID) or Exists(%ShipAgrmID); //wrong
?
I've got 2 rows in 'Shipment', but I need 3. Sorry, maybe my picture is not correct.
Not Exists() is wrong too.
try
where len(trim(%ShipAgrmID))=0 or Exists(%ShipAgrmID)
I've got it!
[Agreement]:
... //load
[ShipAgreement]:
... //load
[Shipment]:
LOAD * INLINE [
%ID, %ShipAgrmID, #Qty
ID1, , 10
ID2, A, 11
ID2, A, 12
ID3, C, 10
]
where %ShipAgrmID = '' or Exists(%ShipAgrmID); //not Null but empty string !
Now it's ok, thank you!
Try using applymaps
LOAD * INLINE [
%ID, #Ag_Q
ID1, 100
ID2, 200
ID3, 250
];
MAP_Agreement:
Mapping Load
%ID, #Ag_Q
Resident
Agreement;
Drop table Agreement;
[ShipAgreement]:
LOAD * INLINE [
%ID, %ShipAgrmID, #Sh_Q
ID2, A, 50
ID2, B, 40
];
MAP_ShipAgreement:
Mapping Load
%ID&'|'&%ShipAgrmID,#Sh_Q
Resident
ShipAgreement;
Drop Table ShipAgreement;
[Shipment]:
LOAD * INLINE [
%ID, %ShipAgrmID, #Qty
ID1, , 10
ID2, A, 11
ID2, A, 12
ID3, C, 13
];
Final:
Noconcatenate Load
%ID, %ShipAgrmID, #Qty
Resident
Shipment
WHERE
Len(Trim(%ShipAgrmID))=0
Or
Isnull(ApplyMap('MAP_ShipAgreement',%ID&'|'&%ShipAgrmID,Null()))=0
;Drop Table Shipment;
What advantage to use applymaps? The code became much complicated. But I like the result!