Generic load with multiple values

[G3S]

hi,

Data looks like this:

POL	POD	Equ	Incoterm	Notes	Carrier	Volume	Ranking
Shanghai	Sydney	General	FOB	-	AA	5	1
Shanghai	Sydney	General	FOB	-	BB	2	2
Shanghai	Sydney	General	FOB	-	CC	1	3
Singapore	Sydney	Reefer	FOB	abcd	CC	10	1
Singapore	Sydney	Reefer	FOB	abcd	XX	7	2

here's the required output - grouped by Carrier/Contract/Volume with 'Ranking' going across:

I've learned that Generic load will do what is required. however, it only works for one value (carrier or contract or volume). 

Is there a way to have multiple values and get the required output?

thanks in advance.

[Taoufiq_Zarra · Accepted solution]

@G3S  One solution :

Input:

LOAD * INLINE [
    POL, POD, Equ, Incoterm, Notes, Carrier, Volume, Ranking
    Shanghai, Sydney, General, FOB,, AA, 5, 1
    Shanghai, Sydney, General, FOB,, BB, 2, 2
    Shanghai, Sydney, General, FOB,, CC, 1, 3
    Singapore, Sydney, Reefer, FOB, abcd, CC, 10, 1
    Singapore, Sydney, Reefer, FOB, abcd, XX, 7, 2
];


CombinedGenericTable:

Load distinct POL&'_'&POD&'_'&Equ&'_'&Incoterm&'_'&Notes as IDtmp resident Input;

for each a in 'Carrier','Volume'

DATA:

generic load POL&'_'&POD&'_'&Equ&'_'&Incoterm&'_'&Notes as IDtmp,'$(a)' &'('&Ranking&')',$(a) resident Input;


FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'DATA.*') THEN 

  LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF 

NEXT i

next

drop table Input;  

Final:
load subfield(IDtmp,'_',1) as POL ,subfield(IDtmp,'_',2) as POD,subfield(IDtmp,'_',3) as Equ,subfield(IDtmp,'_',4) as Incoterm,subfield(IDtmp,'_',5) as Notes,* resident CombinedGenericTable;

drop table CombinedGenericTable;
drop fields IDtmp;

output:

[G3S]

thank you @Taoufiq_Zarra  works percfectly.

If there are other dimensions which  need to be included in the final table&filters, what is the best way to proceed? For example, there is Region, Port Name, Size etc. 

Further I need to sum the volumes per carrier as well. 

I tried a couple of methods but not successful.

[Taoufiq_Zarra]

@G3S 

 you can add it in the IDtmp

or you can share a new sample !

[G3S]

@Taoufiq_Zarra  adding sample

one output is through the generic load:

data for for the first line is.

Region

POL Code

POL

POD

Equ

Incoterm

Notes

Carrier

Type

Ranking

Volume

charge 1

charge 2

charge 3

Route

TransitTime

Contract

Change Date

Rate Type

Europe

FRLEH

Le Havre

AUMEL

General

FOB

abcd

AA

20'

1

7

$           50

$         100

$         100

via A

63

ConAA

1/01/2020

Type1

Europe

FRLEH

Le Havre

AUMEL

General

FOB

abcd

AA

40'

1

7

$           50

$         200

$         200

via A

63

ConAA

1/01/2020

Type1

Europe

FRLEH

Le Havre

AUMEL

General

FOB

abcd

CC

20'

2

5

$           45

$         110

$         110

via B

59

ConCC

25/04/2020

Type1

Europe

FRLEH

Le Havre

AUMEL

General

FOB

abcd

CC

40'

2

5

$           45

$         180

$         180

via B

59

ConCC

25/04/2020

Type1

Filters & further outputs from the above data-set are required.

Filters: Region, POL Code, POL, POD, Equ, Incoterm, Carrier, Change Date 

Further Outputs are to summarise the charges per carrier & by Type / list of changes based on Change Date, MaxTransitTime  etc

thanks,

[Taoufiq_Zarra]

@G3S  by the same logic :

Filters: Region, POL Code, POL, POD, Equ, Incoterm, Carrier, Change Date  -> to add in IDtmp like POL&'_'&POD&'_'&Equ&'_'&Incoterm&'_'&Notes as IDtm

Further Outputs are to summarise the charges per carrier & by Type / list of changes based on Change Date, MaxTransitTime  etc -> to add to
for each a in 'Carrier','Volume'

..

[G3S]

@Taoufiq_Zarra  thank you.  works to add the filters, however some fields' data show mix of dimensions. 

POD is a mixed list of Origin Port Name & POD    /   Cont Type is a mix of POD & Cont Type  / Movement Type is a mix of Movement Type & Cont Type.  screenshot below:

this is the script. Could you please help identify why the mix is occurring?

CombinedGenericTable:

Load distinct Region&'_'&"Port Of Origin"&'_'&"Origin Port Name"&'_'&POD&'_'&"Cont Type"&'_'&"Movement Type"&'_'&"Allocation Comments" as IDtmp
resident Input;

for each a in 'Carrier','"Allocated TEUs"','Contract','"Service Route"','"Cont Size"'

DATA:

generic load

Region&'_'&"Port Of Origin"&'_'&"Origin Port Name"&'_'&POD&'_'&"Cont Type"&'_'&"Movement Type"&'_'&"Allocation Comments" as IDtmp,
'$(a)' &'('&"Allocation Ranking"&')',$(a)
resident Input;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'DATA.*') THEN

LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i

next

Final:

load

subfield(IDtmp,'_',1) as Region ,
subfield(IDtmp,'_',2) as "Port Of Origin" ,
subfield(IDtmp,'_',3) as "Origin Port Name" ,
subfield(IDtmp,'_',4) as POD,
subfield(IDtmp,'_',5) as "Cont Type",
subfield(IDtmp,'_',6) as "Movement Type",
subfield(IDtmp,'_',7) as "Allocation Comments",
* resident CombinedGenericTable;

drop table CombinedGenericTable;
drop fields IDtmp;

exit Script;

however, even previously I was not able to get the part in green below.. volume needs to be each carrier's . not grouped by ranking. worked only if I left join the 'final' table to 'input'.

thanks for your help.

[Taoufiq_Zarra]

@G3S  can you share a sample input and output in excel format so that I can best visualize it?