Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to create a calculated field and populate it with a specific value

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_NUMBERPART_NUMBERSYSTEM_TYPE

100

Option_1
100Option_2
100System_Type_1
100Option_3
500Option_1

500

System_Type_2
500Option_2
500Option_3
500Option_4
700System_Type_3
700Option_1
700Option_2
700Option_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_NUMBERPART_NUMBERSYSTEM_TYPE
100Option_1System_Type_1
100Option_2System_Type_1
100System_Type_1System_Type_1
100Option_3System_Type_1
500Option_1System_Type_2
500System_Type_2System_Type_2
500Option_2System_Type_2
500Option_3System_Type_2
500Option_4System_Type_2
700System_Type_3System_Type_3
700Option_1System_Type_3
700Option_2System_Type_3
700Option_3System_Type_3
1 Solution

Accepted Solutions
mdmukramali
Specialist III
Specialist III

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

277528.PNG

Thanks,

Mukram.

View solution in original post

4 Replies
mdmukramali
Specialist III
Specialist III

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

277528.PNG

Thanks,

Mukram.

Anonymous
Not applicable
Author

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.

mdmukramali
Specialist III
Specialist III

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