SubField two columns simultaneously

[Anonymous]

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:

LOAD

...

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:

LOAD

...

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!

[vishsaggi]

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.

[rwunderlich · Accepted solution]

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:

LOAD

Ship,

SubField(Invoice, ' ') as Invoice

;

LOAD

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://masterssummit.com

http://qlikviewcookbook.com

[Anonymous]

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?

When you load

SubField(Ship, ' ') as Ship,

Invoice

I mean, you get one Ship and multiple Invoice.

But when you after load

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.

[rwunderlich]

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

LOAD

SubField(Ship, ' ') as Ship,

SubField(Invoice, ' ') as Invoice

Inline [

Ship, Invoice

S2 S3, I2 I3

];

Ship	Invoice
S2	I2
S2	I3
S3	I2
S3	I3

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:

LOAD

     SubField(Ship, ' ') as Ship,

     Invoice

Inline [

Ship, Invoice

S2 S3, I2 I3

];

Ship	Invoice
S2	I2 I3
S3	I2 I3

Then load resident for the second SubField:

Data2:

NoConcatenate

LOAD

     Ship,

     SubField(Invoice, ' ') as Invoice

Resident Data;

Ship	Invoice
S2	I2
S2	I3
S3	I2
S3	I3

Wrong answer.

But this produces the desired result:

Data:

LOAD

     Ship,

     SubField(Invoice, ' ') as Invoice

;

LOAD

     SubField(Ship, ' ') as Ship,

     Invoice

Inline [

Ship, Invoice

S2 S3, I2 I3

];

Ship	Invoice
S2	I2
S3	I3

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

http://masterssummit.com

http://qlikviewcookbook.com