topic Re: Issue in following script !! in QlikView

Issue in following script !!

nikhilgarg — Mon, 17 Nov 2014 09:33:12 GMT

Hey,

I am facing an issue in following code:

Data1:

LOAD Months,

Value,

Previous(Value) as Previous_Value,

//MissingCount(Item) as CountOfMissingItem,

FieldValue('Months' , 1) as FName

FROM

(ooxml, embedded labels, table is Sheet1) ;

In the output nothing is printed under the FName column.

Why is it so ? Please xplain.

Thanks.

Re: Issue in following script !!

sujeetsingh — Mon, 17 Nov 2014 09:36:47 GMT

Try sorting the columns first as

Data1:

LOAD Months,

Value,

//MissingCount(Item) as CountOfMissingItem,

FROM

(ooxml, embedded labels, table is Sheet1) ;

Data2:

load * resident Data1 order by Months;

drop table Data1;

Data3:

load

Months,Value,

Previous(Value) as Previous_Value,

FieldValue('Months' , 1) as FName

resident Data2;

Drop table Data2;

Re: Issue in following script !!

nikhilgarg — Mon, 17 Nov 2014 09:41:38 GMT

Hey,

I didnt get your approach. Why cannt we do it in a single table. Why need to make 3 tables for this ??

Re: Issue in following script !!

sujeetsingh — Mon, 17 Nov 2014 09:57:10 GMT

You can minimize the number of the table .

You can use two tables, actually when you are playing with temp table then one temp table do not add a lot if it is omitted after use.

Data1:

LOAD Months,

Value,

Previous(Value) as Previous_Value,

//MissingCount(Item) as CountOfMissingItem,

FieldValue('Months' , 1) as FName

FROM

(ooxml, embedded labels, table is Sheet1)

Order by Months;

Re: Issue in following script !!

nikhilgarg — Mon, 17 Nov 2014 10:00:52 GMT

Hey, By putting Order By Months , it is giving me error.

What to do ??

Re: Issue in following script !!

tresB — Mon, 17 Nov 2014 10:13:32 GMT

Order By works only on resident table.

Try like:

Data1:

LOAD

*
FROM
Example1.1.xlsx
(ooxml, embedded labels, table is Sheet1);

Data2:
Load
Months,
Value,
FieldValue('Months' , 1) as FName
Resident Data1 ; //Order By Months, Value; // You may/may not use order by clause depending on your requirement.

DROP Table Data1;

Re: Issue in following script !!

nikhilgarg — Mon, 17 Nov 2014 10:36:04 GMT

Hey,

Your above code works well but why cannot we do it in a single table. Why we need to make Data2 and resident table ??

Data1:

LOAD Months,

Value,

FieldValue('Months' , 1) as FName ,

FROM

(ooxml, embedded labels, table is Sheet1) ;

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

Why is not above code giving FName value ??

Please tell.

Thanks.

Re: Issue in following script !!

tresB — Mon, 17 Nov 2014 10:44:48 GMT

Possibly because:

FieldValue - Returns the field value found in position n of the field fieldname (by load order)

And this load order comes into play only in resident table.

Re: Issue in following script !!

nikhilgarg — Mon, 17 Nov 2014 10:57:13 GMT

Hey ,

Thanks but what do you mean by Load Order ??

Re: Issue in following script !!

tresB — Mon, 17 Nov 2014 11:06:42 GMT

Load order means - the order in which load is being performed.

You might have data - 1,2,5,4,3

It can be loaded in ascending order(1,2,3,4,5) or descending order(5,4,3,2,1) or as-it-is/without any order by clause(1,2,5,4,3). Possibly, qv doesn't take into consideration the order of the data load directly from data base, and that could make FieldValue() returning null while used without resident load.

Re: Issue in following script !!

Anonymous — Mon, 17 Nov 2014 11:51:32 GMT

Hi Nikhil,

Order by works with Resident table because Qlikview doesn't recognize Order by with Datafiles and QVD's.

Hence we need to load data from Datafiles and QVD's and if Order by required use it on Resident table.

Regards

Nitha

Re: Issue in following script !!

nikhilgarg — Mon, 17 Nov 2014 11:56:02 GMT

So does that mean that let say i have a field: Values having value (1,2,5,4,3).

Then if i load it using Resident Load then data will be loaded in Ascending or descending order ??

Is it so ??

Re: Issue in following script !!

nikhilgarg — Mon, 17 Nov 2014 11:58:06 GMT

So does that mean that let say i have a field: Values having value (1,2,5,4,3).

Then if i load it using Resident Load then data will be loaded in Ascending or descending order ??

Is it so ??

Re: Issue in following script !!

tresB — Mon, 17 Nov 2014 12:04:13 GMT

If you don't mention any sort order it takes ASC (ascending) by default. Otherwise you can explicitly mention like:

Load

Resident <> Order By Asc/Desc FieldName, ...;

Re: Issue in following script !!

Anonymous — Mon, 17 Nov 2014 12:27:37 GMT

Hi,

Yes by default Order by sorts Asc ,you need to specify for Desc.

Regards

Nitha

Re: Issue in following script !!

nikhilgarg — Tue, 18 Nov 2014 05:09:01 GMT

Hey,

Thanks for this explanation.

So , what i understand from above conversation is :

Field value works with Only "Resident Load" and by default it takes the field value in ascending order.

In ordinary load , FieldValue does not works well.

Am i right ??

Re: Issue in following script !!

nikhilgarg — Tue, 18 Nov 2014 05:11:09 GMT

Hey,

Thanks for this explanation.

So , what i understand from above conversation is :

Field value works with Only "Resident Load" and by default it takes the field value in ascending order.

In ordinary load , FieldValue does not works well.

Am i right ??

Re: Issue in following script !!

tresB — Tue, 18 Nov 2014 05:19:15 GMT

Yes, it seems so.

Re: Issue in following script !!

nikhilgarg — Tue, 18 Nov 2014 05:30:51 GMT

THanks.

Re: Issue in following script !!

Anonymous — Wed, 19 Nov 2014 16:55:55 GMT

Hi,

Mostly functions are most effective when used on resident load than on direct loads.

Regards

Neetha