At what point are fields created in the script, available for further interrogation in the script?

[#1]

Hi All,

Can someone kindly explain when does a field become available for further manipulation, when its created in the script?

ie I thought that a field was available as soon as QV has read the ";" within the load statement, but alas not....

Currently I am loading a fact table via a binary load,

Following this, I am creating a "Unique" key and apply this to the original fact table

I then am attempting to "applymap" a field back against this "Unique" key, but guessing QV is not finding this key in the script section (Unique key works well when loading an excel file, and linked via associated field names)

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

Binary ".\Artemis_Qlikdatabase.qvw";

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;

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

For further details on this issue, please also see previous thread:

http://community.qlik.com/message/135080#135080

Any thoughts, greatly appreciated

Kind regards,

Rich

[gandalfgray]

I don't know if it's just here in the post, otherwise you have a syntax error in your script:

There should not be a ","  after "as Unique" if  you do not have more fields to load after that.

[#3]

Hi Gandalf,

Thanks for the above, in fact I have several other "Unique" keys being created following the original, so that was the reason why I accidently left included the "," in the post.

FYI my script appears to do everything that I would expect it to, with exception of:

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

ie if I comment out the above script, the script execution window closes with no error messages. 

By leaving the following script active, my "script execution window" doesn't close in excess of 2hrs (usually takes 15mins to run without the above included).  My issue therefore "must?" lie with the applymap statement / unique field??

[gandalfgray]

Hi Rich

To answer your original question:

Yes, the field is available as soon as QV has read the ";" within the load statement.

To verify that, you could add an "exit script;" immediately after the line where you think the field should have been created.

I think there is something else causing the problems with your script

/gg

[#5]

Hi GG,

As before, thanks for your reply!

I've simplified my issue in the attached document (with exception to the initial binary load), but interestingly once the script executes it highlights that I have a synthetic issue...

Looking at the above image, it would imply that I have multiple fields that exist in both tables.  However, looking at the source files, I was carefull to ensure all fields differ with exception of the "Unique" key...?

Any thoughts?

Kind regards,

Rich

[gandalfgray · Accepted solution]

Seems to me you are "applymapping" back to the ContractedSpend-table?

What if you try this:

ContractedSpend:
MAPPING LOAD

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

// work on a tmp-table

_tmpPurchases:

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

Drop Table Purchases;

Rename Table _tmpPurchases to Purchases;

[#7]

Gandalf,

Many, many thanks for your help!  Have managed to replicate in my sample model as well as my main file!

Out of interest, do you know why the creation of a temp table solved the issue as opposed working with the original table?

Regardless, many thanks once again!

Kind regards,

Rich

[gandalfgray]

Thanks Rich, glad to be able to help!

Well, I can not see that you were working on the original table.

You did not name "Purchases" as target for the LOAD statement, just as a source (RESIDENT Purchases).

If you want to work directly on Purchases, maybe you could do something like (I have not tried this):

LEFT JOIN (Purchases)

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