topic Re: Help Using Nulls and Joins in QlikView

Help Using Nulls and Joins

Wed, 13 Jul 2011 10:35:14 GMT

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

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

Re: Help Using Nulls and Joins

Miguel_Angel_Baeyens — Wed, 13 Jul 2011 10:56:50 GMT

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

Re: Help Using Nulls and Joins

Wed, 13 Jul 2011 11:20:06 GMT

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

Re: Help Using Nulls and Joins

Miguel_Angel_Baeyens — Wed, 13 Jul 2011 11:48:34 GMT

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

Help Using Nulls and Joins

Wed, 13 Jul 2011 11:59:57 GMT

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

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

Re: Help Using Nulls and Joins

johnw — Thu, 14 Jul 2011 00:19:10 GMT

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

Re: Help Using Nulls and Joins

Thu, 14 Jul 2011 14:28:40 GMT

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

Re: Help Using Nulls and Joins

johnw — Thu, 14 Jul 2011 17:44:55 GMT

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.

Re: Help Using Nulls and Joins

Mon, 18 Jul 2011 14:27:42 GMT

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

Re: Help Using Nulls and Joins

johnw — Mon, 18 Jul 2011 16:47:03 GMT

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.

Re: Help Using Nulls and Joins

Tue, 19 Jul 2011 10:28:47 GMT

Hi John,

Thanks for the above!

My "Contracted Table" is something that I have loaded into the script (and therefore excluded from the binary load). Does this then change the approach to be used?

Have played with the above, and can get 2 new fields appended to my "Purchases" table, however, all data rows are currently returning a "Y", ie am unable to generate a suitable script that populates un-matched suppliers to "N"

sample exert of my script:

____________________________________________________________

Binary ".\Artemis_Qlikdatabase.qvw"; // loads the "Purchases" table from another qv doc

ContractedSpend:

LOAD zzContracted_Family,

zzContracted_Class,

zzContracted_SupplierID,

Contracted?,

Unique

FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$);

LEFT JOIN (Purchases)
LOAD
Unique,
Contracted? as ContractedTemp
RESIDENT ContractedSpend
;

LEFT JOIN (Purchases)
LOAD
Unique,
if(len(Contracted?),Contracted?,'N') as Contracted
RESIDENT ContractedSpend
;

DROP TABLE ContractedSpend;

____________________________________________________________

have played around with:

" if(Contracted?='Y','Y','N') as Contracted "

" if(len(Contracted?)=1,'Y','N') as Contracted "

" if(len(Contracted?)=1,Contracted?,'N') as Contracted "

When inserting "ContractedTemp" instead of "Contracted?", QV returns a "field not found" error, which I guess is linked to the fact that the field has not yet been read/loaded into QV.

but guessing the problem lies else where, as "technically" the above statements are correct...?

Any thoughts, greatly appreciated,

Kind regards,

Rich

Re: Help Using Nulls and Joins

pat_agen — Tue, 19 Jul 2011 11:05:56 GMT

hi,

I think you have misread John's post.

The first left join is from your table with contracted spend data,

LEFT JOIN (Spend)

LOAD

Supplier

,Contracted as ContractedTemp

RESIDENT ContractedTable

;

this creates the new Field ContractedTemp wherever there is contracted spend.

The second however is a left join of the spend table to itself.

LEFT JOIN (Spend)

LOAD

the key field(s) to the spending table

,if(len(ContractedTemp),ContractedTemp,'N') as Contracted

RESIDENT Spend

This tests the new field - ContractedTemp. If it exists, fine, it places the information into a new field called Contracted, if not, ie in the case of your spend being non contracted, it puts the value 'N' into the contracted field.

you will have to be careful on the key fields for this second join. Tis is the line John codes as "the key field(s) to the spending table".

Re: Help Using Nulls and Joins

johnw — Tue, 19 Jul 2011 19:22:55 GMT

Since the ContractedSpend table is coming from an Excel file instead of the binary load, we can go back to an applymap. We just need to do it during a left join instead of during the intial load (since the initial load is done by binary load, we can't touch it).

Binary ".\Artemis_Qlikdatabase.qvw";

ContractedSpend:
MAPPING LOAD
zzContracted_SupplierID
,Contracted?
FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
;
LEFT JOIN (Purchases)
LOAD
Unique,
applymap('ContractedSpend',Supplier,'N') as Contracted?
RESIDENT Purchases
;

Re: Help Using Nulls and Joins

Thu, 21 Jul 2011 09:19:47 GMT

Hi both,

Thanks for your time on this, although experiancing a few issues still.

Binary ".\Artemis_Qlikdatabase.qvw";

ContractedSpend:
MAPPING LOAD
zzContracted_SupplierID // I use the "Unique" field as combines the SupplierID with Material, ie if contracted supplier is linked to a random Material, spend is not deemed "Contracted"
,Contracted?
FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
;
LEFT JOIN (Purchases)
LOAD
Unique,
applymap('ContractedSpend',Supplier,'N') as Contracted?
RESIDENT Purchases
; // have used "Unique" instead of Supplier, as this is my key field in my mapping load?

---------------------------------------

Making the above two changes, i get an "Out of Virtual and/or logical memory" error message, which could imply I have a synthetic join?

Can anyone confirm whether my above changes to the script are valid/logical, or whether its these changes that have caused the issue?

Kind regards,

Rich

Re: Help Using Nulls and Joins

pat_agen — Thu, 21 Jul 2011 10:31:58 GMT

hi Rich,

you have miscoded the applymap function.

change your code as follows and see if this improves the situation:

Binary ".\Artemis_Qlikdatabase.qvw";

ContractedSpend:
MAPPING LOAD
zzContracted_SupplierID
,Contracted?
FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
;

     LOAD
     *,
     applymap('ContractedSpend',Unique,'N') as Contracted?
     RESIDENT Purchases
     ;

the applymap looks for your "unique" value in the contractedspend table and takes the value from teh contract? filed if the seaxcrh is sucesful. If not it will put 'N' in the Contracted?

Re: Help Using Nulls and Joins

Thu, 21 Jul 2011 16:35:02 GMT

Hi Pat,

Thanks for this!

Have adjusted my script as prompted (although in the initial "MAPPING LOAD", the key field I am returning in my script is "Unique" vs "zzContracted_SupplierID") - let me know if you think I am incorrect in doing this (ie its a combination of SupplierID and Material - and is a field that exist (and works) in my QV Purchases table?

When running the script, my QV document fails to complete - ie it appears to be correct (ie no error messages are being returned), but my "Script Execution Progress" window does not automatically close, denoting full script has been run.

From what I can see, the applymap routine has been performed as it appears mid-way through the script, so unsure why its not completing. According to the "SEP" window, it took 13mins to run, although window has been open for over 2hrs.

nb by using the "zzContracted_SupplierID" the issue is still present.

Im using QV v8.5 - if that provides any additional insight...?

As before, any thoughts greatly appreciated,

Kind regards,

Rich

Help Using Nulls and Joins

pat_agen — Fri, 22 Jul 2011 08:02:03 GMT

hi,

post the new way in which you have coded your map and subsequent load using the applymap fucntion.

Also make sure this is the area where you are having an issue by comenting out this part of the script and confirming that your error is coming from elsewhere.

Is PMT_Classification the last load in your script?

If so qv is hanging as it tries to build the final datacloud probably due to a circular reference, cartesian join or synthetic key caused by fields inadvertently having the same name in several places.

Re: Help Using Nulls and Joins

Fri, 22 Jul 2011 12:52:38 GMT

Hi Pat,

Thanks for your response!

I can confirm "PMT_Classification" is my last load in my script.
Commenting out my "applymap sequence", my QV reloads the data with no problems in just over 10mins (with no synthetic joins etc...)
Reviewing the "Table Viewer" I cannot identify any duplicate fields that are being introduced as part of the applymap routine (ie 1 common field between tables = "Unique")

----------------------------------------------------------------------------------------

Current script being used (which fails to finish data load):

ContractedSpend:
MAPPING LOAD

Unique
,Contracted?
FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
;

LOAD *,
applymap('ContractedSpend',Unique,'N') as ContractedFlag
RESIDENT Purchases;

--------------------------------------------------------------------------------------------------------

by commenting out the final section of my applymap statement, my reload is still successfull

LOAD *,

applymap('ContractedSpend',Unique,'N') as Contracted?

RESIDENT Purchases;

--------------------------------------------------------------------------------------------------------

one thing i've realised, is that the "Unique" field in the "Purchases" table is being defined in the previous step of the script - could this be causing the issue (ie the field that the applymap is targetting)?

zzTempContractedSpend2:
noconcatenate Load
*,
if (left(zzFamily,1)<>2, zzFamily & '-' & SupplierID , zzFamily & '-' & zzClass & '-' & SupplierID) as Unique, // new field to be added "Unique"
Resident Purchases;

drop table Purchases;
rename table zzTempContractedSpend2 to Purchases;

//---------------------------------------------

ContractedSpend:
MAPPING LOAD

Unique
,Contracted?
FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
;

LOAD *,
applymap('ContractedSpend',Unique,'N') as ContractedTemp
RESIDENT Purchases;

--------------------------------------------------------------------------------------------------------

As before, any thoughts greatly appreciated!

Kind regards,

Rich

Re: Help Using Nulls and Joins

pat_agen — Thu, 04 Aug 2011 06:54:47 GMT

hi,

it's hard to tell from this distance.

However you have isolated the issue as coming from the script which includes the applymap statement.

This statement is adding a new field - Contracted?, altho' in your last example it seems to change names several times in the code you post - to your Purchases table. Now look at th code you have posted where you show how the Unique field is created in the Purchases table, again this is a new field. You start by dong a noconcatenate load into a different temporary table, drop the orignal Purchases table then rename your temporary table. This works fine.

However you do not seem to follow the same sequence when adding the contracted? field. Why not? I am not sure if this is the cause but if you are still stuck why not test that one out?

so your code will look as follows:

ContractedSpend:
MAPPING LOAD

Unique
,Contracted?
FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
;

zzTempTableApplyMap:

noconcatenate LOAD *,
applymap('ContractedSpend',Unique,'N') as ContractedTemp
RESIDENT Purchases;

drop table Purchases:

rename table zzTempTableApplyMap to Purchases;