How-To Remove Data after _ on load

[dustonmoore]

We use our customer numbers in many tables but they are not always kept in the same format. For orders the customer numbers will be 12345_0, 12345_1, 12345_2. The number after the _ represents a different ship to. For credit/debit memos the customer numbers will not have any thing after the primary number for example: 12345 instead of 12345_0. We are using the order customer numbers as a key. Is there a way for me to connect these either on or after load?

[Øystein_Kolsrud]

You might want to look into this function: https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFun...

Using that you should be able to write an expression like this:

SubField(Order, '_', 1)

[dustonmoore]

I see how that would work but could I do this on load so that I could connect the two set of data using the Customer Number key?

[Øystein_Kolsrud]

I'm not sure I understand, but do you mean you would like to create a table like this?

Order	Key	Number
12345_0	12345	0
12345_1	12345	1
12345	12345	-
23456_0	23456	0
23456_1	23456	1

If so, then you should be able to do something like this:

MyTable:
Load
  Order,
  SubField(Order,'_',1) as Key,
  SubField(Order,'_',2) as Number
Resident SourceTable;