Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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.

SubField.png

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!

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: SubField two columns simultaneously

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

View solution in original post

4 Replies
Highlighted
Champion III
Champion III

Re: SubField two columns simultaneously

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: SubField two columns simultaneously

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

View solution in original post

Highlighted
Contributor II
Contributor II

Re: SubField two columns simultaneously

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: SubField two columns simultaneously

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
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:

LOAD

     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

LOAD

     Ship,

     SubField(Invoice, ' ') as Invoice

Resident Data;


Ship Invoice
S2I2
S2I3
S3I2
S3I3

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

http://masterssummit.com

http://qlikviewcookbook.com