Issue in following script !!

[nikhilgarg]

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.

[sujeetsingh]

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;

[nikhilgarg]

Hey,

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

[sujeetsingh]

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;

[nikhilgarg]

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

What to do ??

[tresesco]

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;

[nikhilgarg]

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.

[tresesco]

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.

[nikhilgarg]

Hey ,

Thanks but what do you mean by Load Order ??

[tresesco · Accepted solution]

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.