Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help Using Nulls and Joins

Hi,

I have 2 tables, Spend and Contracted:  first details the supplier and their spend, the second has the supplier and if they are a contracted supplier (Y).

The Spend table contains more suppliers than can be found in the Contracted table

I would like to combine the details found in the "Contracted" table back into the "Spend", and for those suppliers that are not listed in the "Contracted" table, to return "No" by default

contracted.gif

In the table above, I would like suppliers B, D, G, H, I and J all to return "No" under the "ContractedSupplier" field - but for some reason, my script is not working.  Anyone with any thoughts?

Kind regards,

Rich

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

How about a map with a default value?

ContractedTable:
MAPPING LOAD * INLINE [
    Supplier, Contracted
    A, Y
    E, Y
    C, Y
    F, Y
];
Spend:
LOAD *,applymap('ContractedTable',Supplier,'N') as Contracted
INLINE [
    Supplier, Spend
    A, 123
    B, 3132
    C, 243
    D, 345
    E, 345
    F, 345
    G, 435
    H, 345
    I, 435
    J, 345
];

View solution in original post

18 Replies
Miguel_Angel_Baeyens

Hello Rich,

Check the following script. I've removed the precedent load and it's loading all records in one table, using a two step load instead.

Spend:

LOAD * INLINE [

    Supplier, Spend

    A, 123

    B, 3132

    C, 243

    D, 345

    E, 345

    F, 345

    G, 435

    H, 345

    I, 435

    J, 345

];

ContractedTable:

LOAD * INLINE [

    Supplier, Contracted

    A, Y

    E, Y

    C, Y

    F, Y

];

JOIN (Spend) LOAD Supplier,

    Contracted as ContractedX

RESIDENT ContractedTable;

FinalTable:

LOAD Supplier,

     If(Len(ContractedX) = 0, 'N', ContractedX) AS Contracted,

     Spend

RESIDENT Spend;

DROP TABLES Spend, ContractedTable;

Does this make sense to you?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

Hi Miguel,

Thanks for this.  If i wanted to keep my "Spend" table, and include the "Y/N" contracted flag within the Spend table, how do I adjust this - ie my first thoughts were that this would have to be done with a preceding load - but I cannot get it to work...?

Miguel_Angel_Baeyens

Hi Rich,

That preceding load will not work because the joined table doesn't exist yet (it will exist at the end of the load). Although you are doing here two steps, you are keeping all the information from the Spend table, and you can create and modify as many fields as you want.

You can even rename the table to its original Spend name, if that suits you.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

Hi Miguel,

Thanks for your time.  Perhaps i need to clarify a bit further...   In my "real" document, I have 1 large fact table (Purchases), in which I would like to append the "Contracted" Y/N flag against.

I have a separate table which identifies which suppliers are contracted (ie = Y).

What I was wanting to achieve, was simply to join the "contracted" "Y" value to my "Purchases" table, and where no value has been returned, automatically set to "N".

contracted2.gif

As my "Purchases" table is already big / contains a lot of fields, do I therefore need to join all the remaining fields to the "FinalTable" as per your example, and then rename the table back to "Purchases"?

johnw
Champion III
Champion III

How about a map with a default value?

ContractedTable:
MAPPING LOAD * INLINE [
    Supplier, Contracted
    A, Y
    E, Y
    C, Y
    F, Y
];
Spend:
LOAD *,applymap('ContractedTable',Supplier,'N') as Contracted
INLINE [
    Supplier, Spend
    A, 123
    B, 3132
    C, 243
    D, 345
    E, 345
    F, 345
    G, 435
    H, 345
    I, 435
    J, 345
];

Not applicable
Author

Hi John,

Have tailored the above to my data, although I have an issue with the applymap statement, ie my full script runs and finishes only once I have commented out the latter part of the script (ie "Purchases' applymap")

Contracted_Map:
Mapping load
Unique,
Contracted? as ContractedSupplier
resident ContractedSpend;


Purchases:
Load *,
applymap('Contracted_Map',Unique,'N') as ContractedSupplier
resident Purchases;

do you have any thoughts?

Kind regards,

Rich

johnw
Champion III
Champion III

Do the applymap() during the initial load of the purchases table, not after.  One point of this approach is to avoid doing an extra load of any kind.

Not applicable
Author

Hi John,

Thanks for your reply! 

At the moment, the bulk of the data that I am using is generated during the initial binary load, refering back to another QV document.

Due to the above issue, would this now mean that I cannot use the applymap function on this table, or do you know of a workaround?

Your comment re

"One point of this approach is to avoid doing an extra load of any kind." - can you expand on this point, ie are you meaning that no additional load can be performed on the "Purchases" table, once I have used an "applymap" statement, anywhere in my script?  Or just during this immediate part of the script (ie up until the ";" (semi colon)?)

Please advise,

Kind regards,

Rich

johnw
Champion III
Champion III

Ah, OK, if you're loading your tables with a binary load, that's a different animal.  I assume, then, that your contracted table is loaded during the binary load as well?  In that case, I'd probably use left joins.

BINARY blah blah blah;

LEFT JOIN (Spend)
LOAD
Supplier
,Contracted as ContractedTemp
RESIDENT ContractedTable
;
LEFT JOIN (Spend)
LOAD
the key field(s) to the spending table
,if(len(ContractedTemp),ContractedTemp,'N') as Contracted
RESIDENT Spend
;

DROP TABLE ContractedTable;

What I meant by avoiding doing an extra load is that had you NOT been doing a binary load, you wouldn't have needed the left joins above.  There is nothing about doing an applymap() that prevents further loads.  It was a way to NOT NEED further loads.  You're basically doing two extra loads here, the two left joins.  Mind you, you probably get all that time back and more by doing the binary load, so I'm not suggesting you change your overall approach.  I merely misunderstood how you were loading your data. 

The above solution is VERY similar to Miguel's, just with a left join at the end instead of loading a new table.  I believe that will take less RAM than creating a new table.  I'm not certain about CPU, but I'd guess about the same.