use calculated field within same load

[andriesb]

Within a current load statement I would like to use the same calculated (concatenated value) again within the same load statement

My code..

Load

'param1:' & param1 & ';param2:' & param2  as   MyCalcField,

if (  MyCalcField =  'param1:test;param2... )

whereas the variable Calcfield shoud be the same as previous defined. However this code results in an error

Any ideas would be appreciated.

Regards

Andries Bos, The netherlands 

[johnw]

If I've understood, use a preceding load:

LOAD *
,if(MyCalcField='param1:test;param2... )
;
LOAD
'param1:' & param1 & ';param2:' & param2  as   MyCalcField,

[andriesb]

the if statement is within the same load sequence

Load

'param1:' & param1 & ';param2:' & param2  as   MyCalcField,

if (  MyCalcField =  'param1:test;param2... , "result 1', 'result2)  as MyOtherfield

From....  where ...

From your answer, I have to define the same

'param1:' & param1 & ';param2:' & param2 twice?

I thought I could use the parced field within my second if statement. In other words : the outcome of one line could be used within the same Load statement.

Another example where this could be handy (if possible):

Load

applymap (.'mapList1','Param1, ' ')   as MyCalcField,

applymap ('maplist2' , MyCaclField, ' '); as MySecondField,

applymap ('Maplist3' , MysecondField, ' ') as MyThirdField

From....

Is another (only? ) option to reload the Resident Table again and do a substitute the current field value within the second Load time.

,

[johnw]

You don't need to concatenate twice.  You do it once, then reference the result in the preceding load like I showed.

You can't do it in the same load statement because you cannot reference a field that hasn't been created yet.  MyCalcField hasn't been created for the row you are establishing until after that row is established.  QlikView isn't smart enough to determine the right sequence in which to establish interdependent fields, or to flag loops in such interdependencies.  Their solution is to make YOU give the sequence explicitly by using preceding loads.

You may not be understanding what a preceding load is.  When you do a preceding load, one load is taking the other load as input.  You can think of them as occurring in sequence, but so far as I know, QlikView isn't creating temporary tables or anything like that.  I believe it is just loading a single record at a time, and sending it through the whole sequence of loads (there can be many preceding loads) before setting the final values for the rows.  To the best of my knowledge, the preceding load is simply the syntax QlikView uses to do EXACTLY what you intended to do by adding your if() statement to the first load.  So any complaints, to the best of my knowledge, are SYNTAX complaints - behind the scenes, exactly what you want is occurring.  I don't believe performance is harmed.  If anything, performance should be a little better because you are explicitly telling QlikView in which order to establish your fields, saving QlikView the time of figuring out in which order to establish the fields.

Now, QlikView might not be coded as efficiently as it could be, so maybe real world performance could be improved, but I don't really know, and we don't really have any control over it anyway.  The syntax is what the syntax is.  The performance is what the performance is.

For your new example, as I said you can have many preceding loads.  So the syntax would look something like this:

MyTable:
LOAD *, applymap('Maplist4',MyThirdField ,' ') as MyFourthField;
LOAD *, applymap('Maplist3',MySecondField,' ') as MyThirdField;
LOAD *, applymap('Maplist2',MyFirstField ,' ') as MySecondField;
LOAD Customer, Product, MyFirstField
FROM MyTable.qvd (QVD)
;

And as an example of why QlikView probably forces you to explicitly specify the sequence, if you could reference fields that don't exist yet in a load, you could do this:

LOAD *
,FieldA as FieldB
,FieldB as FieldC
,FieldC as FieldD
,FieldD as FieldA

FROM MyTable.qvd (QVD)
;

This is a loop, so would need to return an error.  Even if we replaced the final FieldA with FieldF, it would need to look through all your fields and establish the correct sequence, and with conditions, that sequence could be different for every single row.  These are certainly solvable problems, and I've personally written software (not in QlikView) that does exactly that.  I think QlikView would be a better product if it included that sort of thing.  But I can't remember preceding loads ever causing me problems in practice.  It seems a simple enough syntax.

And don't use a resident load.  That would probably be drastically less efficient than a preceding load.

[andriesb]

Thanks for the explaination. It does help a lot to 'learn' the best practice.

My load has a lot of exceptions (dus to bad data in the database). In total I think i have to add 8 different exceptions.

This is the main reason why I would like to keep my solution readable.

I thought my code would be working as this example explains:

My original code, within the single load statement is to duplicate code :

Load  

'docbedr:'&bron_docbedr&'¡docsoort:'&bron_docsoort&'¡docnr:'&bron_docnr&'¡docregel:'& bron_docregel AS GRB_ID,

if (kode= adres) then

applymap ( 'Maptable', 'docbedr:'&bron_docbedr&'¡docsoort:'&bron_docsoort&'¡docnr:'&bron_docnr&'¡docregel:'& bron_docregel, kode), kode as Kode_ID,

from...

should, according to your sample code, be transformed into

LOAD *,   // 3rd parser

     if (Company_ID= Addres_lable) then applymap ( 'Maptable',GRB_ID , Company_ID), Company_ID as Company_ID;   // use the calculated value within the same field

               // 2nd parser

LOAD *, 'docbedr:'&grb.bron.docbedr&'¡docsoort:'&grb.bron.docsoort&'¡docnr:'&grb.bon.docnr&'¡docregel:'& grb.bron.docregel as GRB_ID;  // calulate the value of GRB_ID

               // 1 st parser

LOAD              // load selected field (not all of them) but at least the fields I need in step 2 and 3

         kode as Company_ID

          GRB_ID,    // to be able to connect to other tabel  field

          addres as Addres_lable,

           aZip as ZipCode,

          bron_docbedr  as grb.bron.docbedr

Explanation coding this way (in my humble opinion):

- This means that the code is parced from buttom to top... After the first inital load, all records loaded via the sql would have been added. The second parcer would calculate the grb_id and the third parse would change the value of one field only if the condition is met.

Also within the second parser, i have to use the field values as defined within the first parser : the original filed names from the source are not known.

Also could use (do I have to) the original filed name to be replated.

I do not need all the fields in the original database , so load * would load all fields within the first two parser steps?

as a programmer I am familiar setting my value like:

load *

if (Company_ID= Addres_lable) then Company_ID := applymap ( 'Maptable',GRB_ID , Company_ID), Company_ID

but this solution, I know,  does not work in scripting...

However, I'm still strugling with this solution as it shows me some sql load  with no specific error indicartion.

Maybe my assumptions I wrote down are wrong within the previous example?

SQL SELECT * from...  WHERE

[johnw]

It doesn't work because you have at least two syntax errors (bad if(), missing comma in field list).  You also don't show all the fields being loaded in the section of script you posted, but I assume they're loaded in the actual script.  I think you want this, but I'm not entirely sure:

LOAD *,
if (Company_ID= Addres_lable,applymap('Maptable',GRB_ID ,Company_ID),Company_ID) as Company_ID;
LOAD *, 'docbedr:'&grb.bron.docbedr&'¡docsoort:'&grb.bron.docsoort&'¡docnr:'&grb.bon.docnr&'¡docregel:'& grb.bron.docregel as GRB_ID;
LOAD kode as Company_ID,
          GRB_ID,
          addres as Addres_lable,
           aZip as ZipCode,
          bron_docbedr  as grb.bron.docbedr

Yes, the loads in this case are parsed from bottom to top.  You're correct that in the second parser, you use the field names given in the first parser.  Each load comes from the earlier output, so only knows the field names assigned in that earlier load.  The load * in your second and third parser load all fields from the previous parser step, but not all fields from the original database.  I'm afraid I don't understand your question, "Also could use (do I have to) the original filed name to be replated."  I THINK you're asking if you could use "kode" like below.  If so, yes, that's perfectly valid.  You don't HAVE to rename the fields in any of the intermediate steps, you just can.  Change names at whatever step is most convenient for you, or just leave it as kode if you prefer kode.

LOAD *,
if (kode= Addres_lable,applymap('Maptable',GRB_ID,kode),kode) as Company_ID;
LOAD *, 'docbedr:'&grb.bron.docbedr&'¡docsoort:'&grb.bron.docsoort&'¡docnr:'&grb.bon.docnr&'¡docregel:'& grb.bron.docregel as GRB_ID;
LOAD kode,

[#7]

New to QlikView, but jumping in with both feet!

Here is my original load:(its larger with many more fields, but these give you the gist)

//********* FSAData Table *********

FSA_BaseDataSAP:

LOAD      [Reward Paid] as RewardPaid,

               [Lead Status]as LeadStatus,

	//********* LeadStage *********
	// APPROVED

If([Reward Paid]>'0'

    ,'APPROVED'

    ,

// ABANDONED

    If([Reward Paid]='0' AND Match([Lead Status],'Abandoned')

        ,'ABANDONED'

        ,

// REJECTED

        If([Reward Paid]='0' AND Match([Lead Status],'Imported in Error','Rejected')

            ,'REJECTED'

            ,

// IN PROGRESS

            If([Reward Paid]='0' AND Match([Lead Status],'AWTG_REV','Imported','In Progress','Pending','Postponed','Promoted')

                ,'IN PROGRESS'

                ,''))))as LeadStage

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

WHERE LEN([SAP Lead Number])=9;

The question: how would I code the following request to use the data within the newly created field LeadStage in the creation of another new field called LeadActioned with the following requirement:

	//********* LeadActioned *********
	// ACTIONED

If([LeadStage]>'0'AND Match([LeadStage],'ABANDONED','APPROVED','PROMOTED','REJECTED')

    ,'ACTIONED'

    ,

    // NOT ACTIONED

    If([LeadStage]>'0'AND Match([LeadStage],'AWTG_REV')

        ,'NOT ACTIONED'

        ,''))as LeadActioned,

I am uncertain how/where to place the load; outside of this load completely? And how do I reference it?

FROM Resident FSA_BaseDataSAP

LOL - and I thought the development of LeadStage was convoluted 😉

Jan