Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Generic load with multiple values

hi,

Data looks like this:

POLPODEquIncotermNotesCarrierVolumeRanking
ShanghaiSydneyGeneralFOB-AA51
ShanghaiSydneyGeneralFOB-BB22
ShanghaiSydneyGeneralFOB-CC13
SingaporeSydneyReeferFOBabcdCC101
SingaporeSydneyReeferFOBabcdXX72

 

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

 

generic load.JPG

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.

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Master
Master

@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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

7 Replies
Highlighted
Master
Master

@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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Highlighted
Contributor III
Contributor III

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.

Highlighted
Master
Master

@G3S 

 you can add it in the IDtmp

or you can share a new sample !

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Contributor III
Contributor III

@Taoufiq_Zarra  adding sample

one output is through the generic load:

generic load1.JPG

data for for the first line is.

RegionPOL CodePOLPODEquIncotermNotesCarrierTypeRankingVolumecharge 1charge 2charge 3RouteTransitTimeContractChange DateRate Type
EuropeFRLEHLe HavreAUMELGeneralFOBabcdAA20'17 $           50 $         100 $         100via A63ConAA1/01/2020Type1
EuropeFRLEHLe HavreAUMELGeneralFOBabcdAA40'17 $           50 $         200 $         200via A63ConAA1/01/2020Type1
EuropeFRLEHLe HavreAUMELGeneralFOBabcdCC20'25 $           45 $         110 $         110via B59ConCC25/04/2020Type1
EuropeFRLEHLe HavreAUMELGeneralFOBabcdCC40'25 $           45 $         180 $         180via B59ConCC25/04/2020Type1

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,

Highlighted
Master
Master

@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'

..

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Contributor III
Contributor III

@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:filters.JPG

 

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'.generic load1.JPG

 

thanks for your help.

Highlighted
Master
Master

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉