Qlik Sense - Help with adding column (script)

[Amit_B]

Hi.

I have a facts table (5M rows) and a dim table (3K rows).
Facts: Customer ID, Request ID, Office ID, Type ID - customer can have multiple requests.

Cust ID	Req ID	Loc ID	Type ID
1	1	110	A
1	2	108	B
2	1	105	B
3	1	118	C
3	2	104	C
3	3	111	A
4	1	106	C
4	2	109	B

Dim: Type ID, Sub Type ID.

Type ID	Sub Type ID
A	a001
B	b002
C	c003

First I wanted to add the Sub Type column to the facts, but after the (left) join there were about 15B rows instead of 5M. I didn't understand why it happened but I used mapping instead. I would like to understand why the join doesn't work.

CustID	ReqID	LocID	TypeID	SubTypeID
1	1	110	A	a001
1	2	108	B	b002
2	1	105	B	b002
3	1	118	C	c003
3	2	104	C	c003
3	3	111	A	a001
4	1	106	C	c003
4	2	109	B	b002

After that, I need to add a new column - if the first request of the customer meets with condition (Left(LocID,2)=11), then the new column will get the value of the Sub Type of the first request for all customer's requests, Null if else.

Cust ID	Req ID	Loc ID	Type ID	Sub Type ID	New
1	1	110	A	a001	a001
1	2	108	B	b002	a001
2	1	105	B	b002	Null
3	1	118	C	c003	c003
3	2	104	C	c003	c003
3	3	111	A	a001	c003
4	1	106	C	c003	Null
4	2	119	B	b002	Null

I need help with adding this column, especially for the rows for request Id  = 2 or above.
I tried to use Group By or Previous without success.
Thank you!

[BrunPierre · Accepted solution]

Hello, with a Left Join, this situation can occur due to the possibility of multiple matches for the same ID in the Dimension table. However, by using the mapping load approach, each row in the "Facts" table will be assigned a unique corresponding ID from the dimension table.

Data:
LOAD * Inline[
CustID,ReqID,LocID,TypeID,SubTypeID
1, 	1, 	110, 	A, 	a001
1, 	2, 	108, 	B, 	b002
2, 	1, 	105, 	B, 	b002
3, 	1, 	118, 	C, 	c003
3, 	2, 	104, 	C, 	c003
3, 	3, 	111, 	A, 	a001
4, 	1, 	106,	C, 	c003
4, 	2, 	109, 	B, 	b002 ];

Left Join(Data)
/*--- FirstRequest ---*/
LOAD CustID,
FirstSortedValue(SubTypeID, ReqID) AS FirstSubTypeID
Resident Data
Group By CustID;

Left Join(Data)
/*--- Condition ---*/
LOAD CustID,
MaxString(If(Left(LocID,2)=11, FirstSubTypeID,'Null')) as New
Resident Data
Group By CustID;

DROP  Field FirstSubTypeID;

EXIT SCRIPT;

[anat]

you can use Applumap function to bring "Sub Type ID" into Fact table.

Map_Dim:

mapping  load  TypeID,SubTypeID from table;

Fact:

load *,applymap('Map_Dim',TypeID) as Sub Type ID from tabl2;

[cristianj23a]

Hello, try this code, it should not affect your amount of data.
If it affects again, check if the data in your relationship fields are the same, sometimes you can differ by a space, for example:
Table Facts:
Type ID = "A " --> Trim("Type ID") = "A"

Dim table
Type ID = "A" --> Trim("Type ID") = "A"
That space can affect the whole model because qliksense is a sense tool, to remove that space use the Trim(Type ID) command.

Here the code:

// Create a map for Sub Type ID
SubTypeMap:
MAPPING LOAD
Trim("Type ID") AS "Type ID",
"Sub Type ID"
RESIDENT DimTable;

// Load the facts without ordering
FactsTmp:
LOAD
"Cust ID",
"Req ID",
"Loc ID",
"Type ID",
ApplyMap('SubTypeMap', Trim("Type ID"), Null()) as "Sub Type ID",
If("Req ID" = 1 and Left("Loc ID",2) = '11', ApplyMap('SubTypeMap', Trim("Type ID"), Null())) as New
INLINE [
Cust ID, Req ID, Loc ID, Type ID
1, 1, 110, A
1, 2, 108, B
2, 1, 105, B
3, 1, 118, C
3, 2, 104, C
3, 3, 111, A
4, 1, 106, C
4, 2, 109, B
] ;

TempTable:
LOAD
"Cust ID",
FirstSortedValue(New, "Req ID") as New
RESIDENT FactsTmp
WHERE "Req ID" = 1
GROUP BY "Cust ID";

// Create a map for New
NewMap:
MAPPING LOAD
"Cust ID",
New
RESIDENT TempTable;

NoConcatenate
Facts:
LOAD
"Cust ID",
"Req ID",
"Loc ID",
"Type ID",
"Sub Type ID",
ApplyMap('NewMap', "Cust ID", Null()) as New
RESIDENT FactsTmp;

Drop Tables FactsTmp, DimTable, TempTable;

Result:

Regarts.

[Amit_B]

Thank you all!