topic Re: SubField two columns simultaneously in QlikView

SubField two columns simultaneously

Anonymous — Thu, 01 Feb 2018 22:49:23 GMT

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!

Re: SubField two columns simultaneously

vishsaggi — Fri, 02 Feb 2018 00:21:16 GMT

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.

Re: SubField two columns simultaneously

rwunderlich — Fri, 02 Feb 2018 05:30:50 GMT

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

Re: SubField two columns simultaneously

Anonymous — Fri, 02 Feb 2018 22:18:07 GMT

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.

Re: SubField two columns simultaneously

rwunderlich — Fri, 02 Feb 2018 23:14:33 GMT

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