How to create a new Custom Dimension in Qlik Sense

[qingkong]

Field   Unrestricted/AVG6 returns results in grouping

How do I write this as a dimension

=IF((Unrestricted/AVG6)<3,"[1-3]",IF((Unrestricted/AVG6)<6,"[3-6]","[>9]"))

Want to achieve the result as shown in the picture below

LOAD
Material,
avg6
FROM [lib://AttachedFiles/AVG6.xlsx]
(ooxml, embedded labels, table is AVG6);

LOAD
Material,
Unrestricted
FROM [lib://AttachedFiles/Mb52_Stock.xlsx]
(ooxml, embedded labels, table is Mb52_Stock);

[micheledenardi · Accepted solution]

MapUnrestricted:
Mapping
LOAD
    Material,
    Unrestricted
FROM [lib://BaseFolder/3.DEV/_Temp/Mb52_Stock.xlsx]
(ooxml, embedded labels, table is Mb52_Stock);

FinalTable:
Load *,
	IF((Unrestricted/avg6)<3,'[1-3]',IF((Unrestricted/avg6)<6,'[3-6]','[>9]')) as Range;
LOAD
	ApplyMap('MapUnrestricted',Material,0) as Unrestricted,
    Material,
    avg6
FROM [lib://BaseFolder/3.DEV/_Temp/AVG6.xlsx]
(ooxml, embedded labels, table is AVG6);

[qingkong]

Dear:

There was an error loading here

[micheledenardi · Accepted solution]

You have to adjust the files path.

[vchuprina · Accepted solution]

Hi, 

You should check your data sources because the Material is not unique, you have a few rows per material in each file.

For instance Mb52_Stock

and AVG6 

If you join data as I did below you will get result of conbinations of avg6 and unrestricted fields

DataTMP:
LOAD Material,
     Unrestricted
FROM
[C:\Users\chuprynav\Downloads\Mb52_Stock.xlsx]
(ooxml, embedded labels, table is Mb52_Stock);

Left Join(DataTMP)
LOAD Material,
     avg6
FROM
[C:\Users\chuprynav\Downloads\AVG6.xlsx]
(ooxml, embedded labels, table is AVG6);

Data:
LOAD
     *,
     IF(Value < 3,'[1-3]',
    IF(Value < 6,'[3-6]','[>6]')) AS Group;
LOAD
     Material,
    Unrestricted,
    avg6,
    Unrestricted/avg6 AS Value
Resident DataTMP;

If you use mapping solution proposed by Michele result will be also wrong because mapping table uses first value per key. In you case for material 10001 you will miss 3310 and 4942

Regards,

Vitalii

[micheledenardi]

I suppose the dataset is incomplete.

He has multiple row per each material probably because MB52 TCode split all materials by warehouse.