How do I combine two fields from two separate files?

[#1]

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!

[Gysbert_Wassenaar]

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

[#3]

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.

[#4]

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?

[#5]

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.

[#6]

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,

[maxgro]

// excel 1

a:

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;

[MarcoWedel]

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

[#9 · Accepted solution]

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!