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

18 Replies
Not applicable
Author

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

pat_agen
Specialist
Specialist

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

johnw
Champion III
Champion III

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
;

Not applicable
Author

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

pat_agen
Specialist
Specialist

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?

Not applicable
Author

error.gifHi 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

pat_agen
Specialist
Specialist

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.

Not applicable
Author

Hi Pat,

Thanks for your response!

  1. I can confirm "PMT_Classification" is my last load in my script.
  2. Commenting out my "applymap sequence", my QV reloads the data with no problems in just over 10mins (with no synthetic joins etc...)
  3. 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

pat_agen
Specialist
Specialist

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;