# 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
Did you mean:
Highlighted
Contributor III

## Generic load with multiple values

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?

Labels (3)

• ### Qlik Sense

1 Solution

Accepted Solutions
Highlighted
Master

@G3S  One solution :

``````Input:

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:

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
7 Replies
Highlighted
Master

@G3S  One solution :

``````Input:

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:

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

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

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

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,

Highlighted
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

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

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:

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:

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",
* 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'.