Preceding Load with calculated field

[kerkying]

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!

[punitpopli]

for Preceding load you can use below syntax :

Temp:

Load *,

    Trim((Column1)) as New_Column;

Load

Column1,

Column2,

.,

.,

From File Name

Hope this help

[Anil_Babu_Samineni]

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;

[kerkying]

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]

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

[#6]

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

[kerkying]

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

[kerkying]

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]

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

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

[Anil_Babu_Samineni]

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;