topic Re: How do I combine two fields from two separate files? in QlikView

How do I combine two fields from two separate files?

Thu, 30 Apr 2015 16:46:01 GMT

Hi everyone! I've been a lurker of these forums for a while now. There is some great stuff here! I decided to post today to hopefully learn a thing or two.

I've been working with Qlikview for a few months now and I've been learning a lot by trial and error. However, I am currently stuck and would love some feedback from you guys. I think my issue is simple, but I'm not sure how to tackle.

I am loading two files (both Excel) into my Qlikview program. Both files share one similar field: ID. One file contains the field OFFERING while the other file contains OFFERINGS.

In most cases, OFFERING and OFFERINGS will match up, but in some cases either OFFERING or OFFERINGS will be blank and needs to be filled with what the other has for data.

Example of data:

ID OFFERING OFFERINGS

123 GBS GBS

124 GIS GIS

125 EGB

126 GBS

127 GIS

128

129 GBS

130 GIS

I'm aiming for my final result look like this:

ID FINAL_OFFERING

123 GBS

124 GIS

125 EGB

126 GBS

127 GIS

128

129 GBS

130 GIS

So the goal is to combine the two into one field named FINAL_OFFERING without removing any of the data I currently have.

Does anyone have a quick fix for me? Thanks a ton in advance!

Re: How do I combine two fields from two separate files?

Gysbert_Wassenaar — Thu, 30 Apr 2015 16:53:57 GMT

Perhaps like this:

Load ID, OFFERING As FINAL_OFFERING From source1 Where Len(Trim(OFFERING))>0;

Load ID, OFFERINGS As FINAL_OFFERING From source2 Where not Exists(ID);

Re: How do I combine two fields from two separate files?

Thu, 30 Apr 2015 16:58:10 GMT

IF(Offering = ''), Offerings, Offering) AS Final_Offering,

If Offering is blank, use Offerings, else use the "Offering" value.

Issue you have to decide - what if there are values in both fields? The above will retain "Offering" if it is not empty

note: could use IsNull(Offering) instead of ''. May need to trim to get rid of spaces in the field.

Re: How do I combine two fields from two separate files?

Thu, 30 Apr 2015 17:06:52 GMT

This is close, but I have two different loads, which I think is my problem.

On one tab of my code, I load OFFERING (from file 1). On another tab, I load OFFERINGS (from file 2).

Thoughts?

Re: How do I combine two fields from two separate files?

Thu, 30 Apr 2015 17:08:21 GMT

I attempted this, but my code seemed to break after the "From" in your code above. Do I need to place anything before or after this section of code?

Sorry, probably a stupid question. Still getting the hang of this.

EDIT: I replaced the words "source1" and "source2" with my actual sources. Still broke.

Re: How do I combine two fields from two separate files?

Thu, 30 Apr 2015 17:10:25 GMT

Assuming you have joined the two tables, in the 2nd load, use the above IF stmt.

table1:

Load * ....

Offeriings

...

Table 2:

Join load (table1)

IF(Offering = ''), Offerings, Offering) AS Final_Offering,

Re: How do I combine two fields from two separate files?

maxgro — Thu, 30 Apr 2015 19:42:04 GMT

// excel 1

load ID , OFFERING inline [

ID , OFFERING, OFFERINGS

123 , GBS , GBS

124 , GIS , GIS

125 , , EGB

126 , GBS,

127 , GIS,

128 , ,

129 , , GBS

130 , GIS,

];

// excel 2

join (a)

load ID , OFFERINGS inline [

ID , OFFERING, OFFERINGS

123 , GBS , GBS

124 , GIS , GIS

125 , , EGB

126 , GBS,

127 , GIS,

128 , ,

129 , , GBS

130 , GIS,

];

final:

load ID, if(len(trim(OFFERING))=0, OFFERINGS, OFFERING) as FINAL_OFFERING

Resident a;

DROP Table a;

Re: How do I combine two fields from two separate files?

MarcoWedel — Thu, 30 Apr 2015 21:10:46 GMT

Hi,

another solution could be:

table1:
LOAD * INLINE [
    ID, OFFERING
    123, GBS
    124, GIS
    125,
    126, GBS
    127, GIS
    128
    129,
    130, GIS
];

Join (table1)
//table2:
LOAD * INLINE [
    ID, OFFERINGS
    123, GBS
    124, GIS
    125, EGB
    126,
    127,
    128,
    129, GBS
    130,
];

Join (table1)
LOAD ID,
    RangeMaxString(OFFERING, OFFERINGS) as FINAL_OFFERING
Resident table1;

hope this helps

regards

Marco

Re: How do I combine two fields from two separate files?

Thu, 30 Apr 2015 21:29:56 GMT

Thanks for the responses.

I have over 40,000 rows of data that would likely cause the last two suggestions to take far too long to do...

I think I've found a quick solution for myself that seems to be working...

LOAD

ID,

. . .,

OFFERING AS FINAL_OFFERING,

. . .

FROM . . .;

LOAD

ID,

. . .,

OFFERINGS AS FINAL_OFFERING,

. . .

FROM . . .;

Seems to have done the trick... Thanks for all the suggestions!