Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I'm new to Qlik Sense and have found this forum extremely helpful. Unfortunately I cannot find an answer to my specific problem below:
I have a spreadsheet that consists of many Sales Order numbers. Each Sales Order number consists of many part numbers making up the entire order. The list of part numbers for each Sales Order number consists of a "primary system type" part number along with a number of "Options" part numbers. I've attached a simplified version of what I am looking at to provide clarification.
SALES_ORDER_NUMBER | PART_NUMBER | SYSTEM_TYPE |
---|---|---|
100 | Option_1 | |
100 | Option_2 | |
100 | System_Type_1 | |
100 | Option_3 | |
500 | Option_1 | |
500 | System_Type_2 | |
500 | Option_2 | |
500 | Option_3 | |
500 | Option_4 | |
700 | System_Type_3 | |
700 | Option_1 | |
700 | Option_2 | |
700 | Option_3 |
In the original spreadsheet the SYSTEM_TYPE column doesn't exist. I would like to create this column in the script and populate the cells with the "primary system type" part number for all rows containing the same Sales Order Number. Below is the end result I am trying to get to:
SALES_ORDER_NUMBER | PART_NUMBER | SYSTEM_TYPE |
---|---|---|
100 | Option_1 | System_Type_1 |
100 | Option_2 | System_Type_1 |
100 | System_Type_1 | System_Type_1 |
100 | Option_3 | System_Type_1 |
500 | Option_1 | System_Type_2 |
500 | System_Type_2 | System_Type_2 |
500 | Option_2 | System_Type_2 |
500 | Option_3 | System_Type_2 |
500 | Option_4 | System_Type_2 |
700 | System_Type_3 | System_Type_3 |
700 | Option_1 | System_Type_3 |
700 | Option_2 | System_Type_3 |
700 | Option_3 | System_Type_3 |
Dear Ben,
if you have only one system_Type per Sales_Order_Number You can try by using ApplyMap
below is the script:
DATA:
LOAD [SALES_ORDER_NUMBER],
[PART_NUMBER]
FROM [lib://TestData]
(html, codepage is 1252, embedded labels, table is @1);
//Where Wildmatch([PART_NUMBER],'System_Type*');
SYSTEM_TYPE_MAP:
Mapping
Load
[SALES_ORDER_NUMBER],
[PART_NUMBER] as [SYSTEM_TYPE]
Resident DATA Where Wildmatch([PART_NUMBER],'System_Type*');
Drop Table DATA;
DATA:
LOAD [SALES_ORDER_NUMBER],
[PART_NUMBER],
ApplyMap('SYSTEM_TYPE_MAP',[SALES_ORDER_NUMBER]) AS [SYSTEM_TYPE]
FROM [lib://TestData]
(html, codepage is 1252, embedded labels, table is @1);
Thanks,
Mukram.
Dear Ben,
if you have only one system_Type per Sales_Order_Number You can try by using ApplyMap
below is the script:
DATA:
LOAD [SALES_ORDER_NUMBER],
[PART_NUMBER]
FROM [lib://TestData]
(html, codepage is 1252, embedded labels, table is @1);
//Where Wildmatch([PART_NUMBER],'System_Type*');
SYSTEM_TYPE_MAP:
Mapping
Load
[SALES_ORDER_NUMBER],
[PART_NUMBER] as [SYSTEM_TYPE]
Resident DATA Where Wildmatch([PART_NUMBER],'System_Type*');
Drop Table DATA;
DATA:
LOAD [SALES_ORDER_NUMBER],
[PART_NUMBER],
ApplyMap('SYSTEM_TYPE_MAP',[SALES_ORDER_NUMBER]) AS [SYSTEM_TYPE]
FROM [lib://TestData]
(html, codepage is 1252, embedded labels, table is @1);
Thanks,
Mukram.
Thanks Mukram,
As an extension to this, let's say there is an additional SALES_ORDER_NUMBER = 900. In this sales order number there are 4 rows for PART_NUMBER = "Option_1", "Option_2", "Option_3" and "Option_4". In this case there is no "primary system type". In this case I'd like to populate the SYSTEM_TYPE with "No_Primary_System". Any insight on how to achieve this would be very much appreciated.
Dear Ben,
In the ApplyMap pass the second parameter:
if no systme_Type available then it will show "No_Primary_System"
ApplyMap('SYSTEM_TYPE_MAP',[SALES_ORDER_NUMBER],'No_Primary_System') AS [SYSTEM_TYPE]
Thanks,
Mukram