Solved: Transforming empty values if conditions are met - Qlik Community

Enhpad · ‎2023-08-15

I want to write a piece of Qliksense script that if a certain condition is met, then fill in the empty BearingCapacity values within the field. Condition I want: If _SimilarID is the same _SimilarID as previous record, but BearingCapacity is empty, then fill in the most common value from BearingCapacity per _SimilarID.
I think this is achievable by using Mode() and Peek(), yet I have not been able to do this successfully.

Hope anyone can help me achieve the wanting result with a piece of script of helping me in the right direction.

I want to turn my dataset from:
Data:
InlineData: LOAD * INLINE [ Key, _SimilarID, OriginalBearingCapacity, EmptyBearingCapacity, Article, ProductCode, Foilthickness, Material_item, Material_posibilities 1087|PS|0,8, 141, , True, 2004223, 1087, 0,8, PS, Recycled 1087|PS|0,8, 141, , True, 2003321, 1087, 0,8, PS, Recycled 1087|PS|0,85, 723, , True, 2004360, 1087, 0,85, PS, Recycled 1087|PS|0,9, 730, 9000, False, 2003474, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, False, 2001136, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, False, 2000036, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, False, 2005771, 1087, 0,9, PS, PCR 1087|PS|0,9, 730, 7000, False, 2000035, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, False, 2006929, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, , True, 2002332, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, , True, 2002635, 1087, 0,9, PS, 1087|PS|0,9, 730, , True, 2004947, 1087, 0,9, PS, 1087|PS|0,9, 730, , True, 2006573, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, , True, 2006930, 1087, 0,9, PS, Recycled 1087|PS|1, 732, , True, 2001686, 1087, 1, PS, 1087|PS|1, 732, , True, 2004600, 1087, 1, PS, 1087-REC|PS|1, 138, 9000, False, 2006183, 1087-REC, 1, PS, 1937|PS|0,9, 1524, , True, 2005540, 1937, 0,9, PS, Recycled 1937|PS|0,9, 1524, , True, 2005571, 1937, 0,9, PS, Recycled 1937|PET|0,7, 1527, 4000, True, 2002715, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, 4000, True, 2003282, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, , True, 2003523, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, , True, 2003942, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, , True, 2004266, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, , True, 2005616, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, , True, 2004826, 1937, 0,7, PET, Recycled 1937|PS|1,4, 1555, , True, 2005340, 1937, 1,4, PS, Recycled ];

Into:
LOAD * INLINE [ Key, _SimilarID, OriginalBearingCapacity, EmptyBearingCapacity, Article, ProductCode, Foilthickness, Material_item, Material_posibilities 1087|PS|0,8, 141, , True, 2004223, 1087, 0,8, PS, Recycled 1087|PS|0,8, 141, , True, 2003321, 1087, 0,8, PS, Recycled 1087|PS|0,85, 723, , True, 2004360, 1087, 0,85, PS, Recycled 1087|PS|0,9, 730, 9000, False, 2003474, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, False, 2001136, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, False, 2000036, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, False, 2005771, 1087, 0,9, PS, PCR 1087|PS|0,9, 730, 7000, False, 2000035, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, False, 2006929, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, True, 2002332, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, True, 2002635, 1087, 0,9, PS, 1087|PS|0,9, 730, 7000, True, 2004947, 1087, 0,9, PS, 1087|PS|0,9, 730, 7000, True, 2006573, 1087, 0,9, PS, Recycled 1087|PS|0,9, 730, 7000, True, 2006930, 1087, 0,9, PS, Recycled 1087|PS|1, 732, , True, 2001686, 1087, 1, PS, 1087|PS|1, 732, , True, 2004600, 1087, 1, PS, 1087-REC|PS|1, 138, 9000, False, 2006183, 1087-REC, 1, PS, 1937|PS|0,9, 1524, , True, 2005540, 1937, 0,9, PS, Recycled 1937|PS|0,9, 1524, , True, 2005571, 1937, 0,9, PS, Recycled 1937|PET|0,7, 1527, 4000, True, 2002715, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, 4000, True, 2003282, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, 4000, True, 2003523, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, 4000, True, 2003942, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, 4000, True, 2004266, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, 4000, True, 2005616, 1937, 0,7, PET, Recycled 1937|PET|0,7, 1527, 4000, True, 2004826, 1937, 0,7, PET, Recycled 1937|PS|1,4, 1555, , True, 2005340, 1937, 1,4, PS, Recycled ];

Enhpad · ‎2023-08-16

Thank you for replying, I had to do a few small tweaks and now it works:

Changes I made:

From:
Left Join (Data)
LOAD
_SimilarID,
Mode(EmptyBearingCapacity) as ModeBearingCapacity
Resident Data
Where len(EmptyBearingCapacity) > 0
Group By _SimilarID

To:
Left Join (Data)
MostCommonBearingCapacity:
LOAD
_SimilarID,
Mode(BearingCapacity) as ModeBearingCapacity
Resident FinalTable
Where len(BearingCapacity) > 0
Group By _SimilarID
order by [_SimilarID] asc
;

View solution in original post

rwunderlich · ‎2023-08-15

Perhaps:

Data:
LOAD * INLINE [ Key, _SimilarID, OriginalBearingCapacity, EmptyBearingCapacity, Article, ProductCode, Foilthickness, Material_item, Material_posibilities
1087|PS|0,8, 141, , True, 2004223, 1087, 0,8, PS, Recycled 1087|PS|0,8, 141, , True, 2003321, 1087, 0,8, PS, Recycled
1087|PS|0,85, 723, , True, 2004360, 1087, 0,85, PS, Recycled
1087|PS|0,9, 730, 9000, False, 2003474, 1087, 0,9, PS, Recycled
1087|PS|0,9, 730, 7000, False, 2001136, 1087, 0,9, PS, Recycled
1087|PS|0,9, 730, 7000, False, 2000036, 1087, 0,9, PS, Recycled
1087|PS|0,9, 730, 7000, False, 2005771, 1087, 0,9, PS, PCR
1087|PS|0,9, 730, 7000, False, 2000035, 1087, 0,9, PS, Recycled
1087|PS|0,9, 730, 7000, False, 2006929, 1087, 0,9, PS, Recycled
1087|PS|0,9, 730, , True, 2002332, 1087, 0,9, PS, Recycled
1087|PS|0,9, 730, , True, 2002635, 1087, 0,9, PS,
1087|PS|0,9, 730, , True, 2004947, 1087, 0,9, PS,
1087|PS|0,9, 730, , True, 2006573, 1087, 0,9, PS, Recycled
1087|PS|0,9, 730, , True, 2006930, 1087, 0,9, PS, Recycled
1087|PS|1, 732, , True, 2001686, 1087, 1, PS, 1087|PS|1, 732, , True, 2004600, 1087, 1, PS,
1087-REC|PS|1, 138, 9000, False, 2006183, 1087-REC, 1, PS,
1937|PS|0,9, 1524, , True, 2005540, 1937, 0,9, PS, Recycled
1937|PS|0,9, 1524, , True, 2005571, 1937, 0,9, PS, Recycled
1937|PET|0,7, 1527, 4000, True, 2002715, 1937, 0,7, PET, Recycled
1937|PET|0,7, 1527, 4000, True, 2003282, 1937, 0,7, PET, Recycled
1937|PET|0,7, 1527, , True, 2003523, 1937, 0,7, PET, Recycled
1937|PET|0,7, 1527, , True, 2003942, 1937, 0,7, PET, Recycled
1937|PET|0,7, 1527, , True, 2004266, 1937, 0,7, PET, Recycled
1937|PET|0,7, 1527, , True, 2005616, 1937, 0,7, PET, Recycled
1937|PET|0,7, 1527, , True, 2004826, 1937, 0,7, PET, Recycled
1937|PS|1,4, 1555, , True, 2005340, 1937, 1,4, PS, Recycled ];

Left Join (Data) LOAD
_SimilarID,
Mode(EmptyBearingCapacity) as ModeBearingCapacity
Resident Data
Where len(EmptyBearingCapacity) > 0
Group By _SimilarID
;

Data2:
LOAD
Key,
_SimilarID,
OriginalBearingCapacity,
if (EmptyBearingCapacity = '' and not IsNull(ModeBearingCapacity), ModeBearingCapacity, EmptyBearingCapacity) as EmptyBearingCapacity,
Article,
ProductCode,
Foilthickness,
Material_item,
Material_posibilities
Resident Data;

Drop Field ModeBearingCapacity;
Drop Table Data;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Enhpad · ‎2023-08-16

Thank you for replying, I had to do a few small tweaks and now it works:

Changes I made:

From:
Left Join (Data)
LOAD
_SimilarID,
Mode(EmptyBearingCapacity) as ModeBearingCapacity
Resident Data
Where len(EmptyBearingCapacity) > 0
Group By _SimilarID

To:
Left Join (Data)
MostCommonBearingCapacity:
LOAD
_SimilarID,
Mode(BearingCapacity) as ModeBearingCapacity
Resident FinalTable
Where len(BearingCapacity) > 0
Group By _SimilarID
order by [_SimilarID] asc
;

Transforming empty values if conditions are met

Data Prep