topic Preceding Load with calculated field in QlikView

Preceding Load with calculated field

kerkying — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Preceding Load with calculated field

punitpopli — Fri, 31 Mar 2017 04:14:01 GMT

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

Anil_Babu_Samineni — Fri, 31 Mar 2017 04:42:44 GMT

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;

Re: Preceding Load with calculated field

kerkying — Thu, 06 Apr 2017 01:51:15 GMT

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

Thanks.

Re: Preceding Load with calculated field

stabben23 — Thu, 06 Apr 2017 06:11:33 GMT

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

Re: Preceding Load with calculated field

Thu, 06 Apr 2017 06:22:10 GMT

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

Re: Preceding Load with calculated field

kerkying — Thu, 06 Apr 2017 06:39:21 GMT

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

Re: Preceding Load with calculated field

kerkying — Thu, 06 Apr 2017 06:46:17 GMT

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?

Re: Preceding Load with calculated field

stabben23 — Thu, 06 Apr 2017 06:57:06 GMT

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

Anil_Babu_Samineni — Thu, 06 Apr 2017 07:29:55 GMT

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;

Re: Preceding Load with calculated field

kerkying — Thu, 06 Apr 2017 08:14:55 GMT

Okay the entire solution is:

We have a schema that stores 2 fields, Physical Tables and Physical Columns. The data contained in all the tables is too large (5000 tables x ~2M rows of data) to be loaded in 1 QVW.

We have a requirement to allow users to do adhoc selection of any column to load it as a table view. It is sort of like choosing, for example, 8 columns from 3 tables out of a possible 10,000+ columns.

Because of this we had 2 list boxes to list out the Physical Tables and the Physical Columns. Users will make their selection and when they are done, click a button to export it into a .qvo config file.

What we see so far is to:

1. User makes selection

2. Export selection to .qvo file

3. Load the selection as new fields

4. Use the values from the new fields to load the actual data from the source QVD.

//Load the values from the fields to extract the actual data from the QVD.

REFERENCE:

LOAD

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

;

//Load the user selected fields

LOAD

[Physical Table Name],

[Physical Column Name]

FROM

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

Right now what is failing is part 4. where the script does not recognize the formula / execute the formula while running the load.