Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
qingkong
Contributor II
Contributor II

How to create a new Custom Dimension in Qlik Sense

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);

结果.JPG

3 Solutions

Accepted Solutions
micheledenardi
Specialist II
Specialist II

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);
Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

micheledenardi
Specialist II
Specialist II

You have to adjust the files path.

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

vchuprina
Specialist
Specialist

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

vchuprina_0-1651235289234.png

and AVG6 

vchuprina_1-1651235406411.png

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

vchuprina_0-1651235289234.png

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").

View solution in original post

5 Replies
micheledenardi
Specialist II
Specialist II

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);
Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
qingkong
Contributor II
Contributor II
Author

Dear:

There was an error loading here

micheledenardi
Specialist II
Specialist II

You have to adjust the files path.

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
vchuprina
Specialist
Specialist

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

vchuprina_0-1651235289234.png

and AVG6 

vchuprina_1-1651235406411.png

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

vchuprina_0-1651235289234.png

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
micheledenardi
Specialist II
Specialist II

I suppose the dataset is incomplete.

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

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.