Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Partner
Partner

Preceding Load with calculated field

Hi,

I am trying to load a calculated field with reference to a field loaded in a previous preceding load.

LOAD

FieldValue('Physical Column Name',1) AS TEST

FROM C:\Users\qvdTest.qvd (qvd);

REFERENCE:

LOAD [Physical Table Name],

     [Physical Column Name]

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

I know that there is something wrong with the script but I don't wish to use something like LET vVariable1 = FieldValue('Physical Column Name',1) and load the variable as I potentially will have to create a lot of variables.

Any advice would be greatly appreciated thanks!

10 Replies
punitpopli
Contributor III

Re: Preceding Load with calculated field

for Preceding load you can use below syntax :

Temp:

Load *,

    Trim((Column1)) as New_Column;

Load

Column1,

Column2,

.,

.,

From File Name


Hope this help

Re: Preceding Load with calculated field

May be one solution would be this with out preceding load?

REFERENCE:

LOAD [Physical Table Name],

     [Physical Column Name]

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

QVDDirectory:

LOAD [Physical Column Name]

FROM C:\Users\qvdTest.qvd (qvd);

FinalDirectory:

Left Join(REFERENCE)

Load FieldValue('Physical Column Name',1) AS TEST // Here TEST returns only first field value of given field

Resident QVDDirectory;

Drop Tables REFERENCE, QVDDirectory;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: Preceding Load with calculated field

Thanks for the reply. But I am asking how to load a calculated field that uses the previously loaded field above. Any ideas?

Thanks.

stabben23
Honored Contributor

Re: Preceding Load with calculated field

Hi,

Punil is showing the correct way, you just need to use Your code, like this:

REFERENCE:

LOAD

*,

FieldValue('Physical Column Name',1) AS TEST

;

LOAD

[Physical Table Name],

[Physical Column Name]

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

In preceding load you load it from bottom and up, so you can use TEST in a load above Preceding Load aso..

Not applicable

Re: Preceding Load with calculated field

Hi Please try below script;It will helpful for you.

REFERENCE:

LOAD

FieldValue('Physical Column Name',1) AS TEST,

* ;

LOAD [Physical Table Name],

     [Physical Column Name]

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

Partner
Partner

Re: Preceding Load with calculated field

Hi Pankaj

Thanks for the reply. Why was there the inclusion of the * for the load? I understand the preceding load but I can't get the FieldValue function to work within the load script. Load * will load all the fields in my QVD which is not desired.

I have a previous selection of column names eg. Column A and Column B that I want to load from the QVD. That is why I want to only load the FieldValue('Physical Column Name'1)

ACTUAL:

LOAD

FieldValue('Physical Column Name',1) AS TEST,

*

FROM C:\Users\kerk.ying.ng\Desktop\Adhoc Testing\qvdHSCACustomsRuling.qvd (qvd);

REFERENCE:

LOAD [Physical Table Name],

     [Physical Column Name]

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

Partner
Partner

Re: Preceding Load with calculated field

Hi Staffan,

FieldValue('Physical Column Name',1) will return me the corresponding 1st row for the Field 'Physical Table Name' previously loaded.

Eg. FieldValue('Physical Column Name',1) = ValueA

The desired outcome is to load only:

LOAD

ValueA

FROM C:\qvd.qvd (qvd);

I get the point about preceding load, thanks. Any advice for acheiving the above?

stabben23
Honored Contributor

Re: Preceding Load with calculated field

Do you want to connect "ValueA" to the REFERENCE table?

Do you have some example data on how the Output should be.

Re: Preceding Load with calculated field

Are you still Troubling? Here the * Indicates in case future customer want all fields then what you do? And it would be raise the error because you are considering as Name in colon. Qlikview wont' capable to identify. You would need to declare the Name which your field - [Physical Column Name] then Qlik can understand easily to sort and output the tackle.

Second option should be this? Still not sure why you want to continue with Preceding Load. Apart from here, I gave 2 options in fact, Instead of preceding load we can go these ways and no performance issues also. Tested done

REFERENCE:

LOAD [Physical Table Name],

     [Physical Column Name]

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

LET NoOfRows = NoOfRows('REFERENCE')-1;

for i=0 to $(NoOfRows)

LET vSingleValue = FieldValue('[Physical Column Name]',1);

LOAD * ,'$(vSingleValue)' as Test

Resident REFERENCE;

next i;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)