Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## SubField two columns simultaneously

Hello  guys, I need help with SubField formula.

I have a table with two column. We can't change this format. So, we can have 2 or more values in one cell. I can't manage this process, so users can input even 10 values in one cell. But they always use spaces as separators.

At first I loaded only one column with SubField formula. As:

...

SubField("Shipment Nr",' ') as ShipmentNr

I didn't load Invoice Nr column.

But now I need this second column values! And I have a problem, because first value in Shipment Nr cell  correlates to first value value in Invoice Nr and so on.

So I can't do this:

...

SubField("Shipment Nr",' ') as ShipmentNr,

SubField("Invoice Nr",' ') as InvoicetNr,

There will be full join with 4 combination, but I need only 2 combination:

Shipment                         Invoice

3159965                             15715537

3159964                             15715538

I will be very grateful for your help!

1 Solution

Accepted Solutions

In each row, Is there always an equal number of multiple values in both field? If yes, I think you can solve it with a preceding load like this.

Data:

Ship,

SubField(Invoice, ' ') as Invoice

;

SubField(Ship, ' ') as Ship,

Invoice

Inline [

Ship, Invoice

S1, I1

S2 S3, I2 I3

S4, I4

]

;

If on some rows there are different number of values, say 2 shipments and 1 invoice, this will not work.  That's another problem.

-Rob

http://qlikviewcookbook.com

4 Replies
Champion III

Can you explain this below text little more please....

"There will be full join with 4 combination, but I need only 2 combination:"

Can you share some sample data as text rather than an Image and your expected output please.

In each row, Is there always an equal number of multiple values in both field? If yes, I think you can solve it with a preceding load like this.

Data:

Ship,

SubField(Invoice, ' ') as Invoice

;

SubField(Ship, ' ') as Ship,

Invoice

Inline [

Ship, Invoice

S1, I1

S2 S3, I2 I3

S4, I4

]

;

If on some rows there are different number of values, say 2 shipments and 1 invoice, this will not work.  That's another problem.

-Rob

http://qlikviewcookbook.com

Anonymous
Not applicable
Author

Yes, it is absolutely what I want! Thank you.

But I don't understand why such a difference from my script:

SubField("Shipment Nr",' ') as ShipmentNr,

SubField("Invoice Nr",' ') as InvoiceNr

In your case Qlik understands what I want, but how it  works?

SubField(Ship, ' ') as Ship,

Invoice

I mean, you get one Ship and multiple Invoice.

Ship,

SubField(Invoice, ' ') as Invoice

you get one ship and one invoice without full join. First value to first value,second value to second value and so on,

It is fantastic, but I don't understand how it works.

It's mysterious to me as well.  I sort of understand why the first case produces the join-like behavior.

SubField(Ship, ' ') as Ship,

SubField(Invoice, ' ') as Invoice

Inline [

Ship, Invoice

S2 S3, I2 I3

];

Ship Invoice
S2I2
S2I3
S3I2
S3I3

It's like the SubFields are executed nested. The first Subfield generates two rows and each those rows in turn executes the second subfield which each generate two rows. It kind of makes sense although it's not how I would want it to work.

Why does the preceding load resolve the duplication problem? I don't know. For example, if you model the preceding load as two separate loads it doesn't work.

The first table:

Data:

SubField(Ship, ' ') as Ship,

Invoice

Inline [

Ship, Invoice

S2 S3, I2 I3

];

Ship Invoice
S2I2 I3
S3I2 I3

Then load resident for the second SubField:

Data2:

NoConcatenate

Ship,

SubField(Invoice, ' ') as Invoice

Resident Data;

Ship Invoice
S2I2
S2I3
S3I2
S3I3

But this produces the desired result:

Data:

Ship,

SubField(Invoice, ' ') as Invoice

;

SubField(Ship, ' ') as Ship,

Invoice

Inline [

Ship, Invoice

S2 S3, I2 I3

];

Ship Invoice
S2I2
S3I3

Correct!

There is something in the implementation of preceding load processing that allows this work.  I wish I could explain it  Perhaps hic‌ can provide an answer?

-Rob