topic Re: Field not found in App Development

Field not found

jyothish8807 — Mon, 22 Sep 2014 05:42:12 GMT

Hi All,

Iam geeting an error field not found [Cont_Year]. Can anyone help?

tab1:
LOAD
[Date of contract],
[Type of contract],
User,
rowno() as Sino
FROM

(ooxml, embedded labels, table is Лист1);

tab2:
load
User,
Year([Date of contract]) as [Cont_Year],
[Cont_Year]&''&[Type of contract]&''&User as Test,
if([Test]=previous([Test]),'Old User','New User') as User_Status

resident tab1;

Regards

Re: Field not found

jagan — Mon, 22 Sep 2014 06:12:06 GMT

Hi,

Try like this

tab1:
LOAD
[Date of contract],
[Type of contract],
User,
rowno() as Sino
FROM

(ooxml, embedded labels, table is Лист1);

tab2:

load

[Cont_Year]&''&[Type of contract]&''&User as Test;
load
User,
Year([Date of contract]) as [Cont_Year],
if([Test]=previous([Test]),'Old User','New User') as User_Status

Regards,

Jagan.

Re: Field not found

jyothish8807 — Mon, 22 Sep 2014 06:20:21 GMT

Hi jagan,

Thanks alot for your reply but now its saying

field not found Test.

I have added resident tab1 in the end.

Reagrds

Re: Field not found

jagan — Mon, 22 Sep 2014 06:35:58 GMT

Hi,

Try this

tab1:
LOAD
[Date of contract],
[Type of contract],
User,
rowno() as Sino
FROM

(ooxml, embedded labels, table is Лист1);

tab2:

LOAD

if([Test]=previous([Test]),'Old User','New User') as User_Status;

LOAD

[Cont_Year]&''&[Type of contract]&''&User as Test;

load
User,

[Type of contract],
Year([Date of contract]) as [Cont_Year]
resident tab1;

Regards,

Jagan.

Re: Field not found

jyothish8807 — Mon, 22 Sep 2014 06:58:17 GMT

Hi Jagan, Thanks alot.It worked.

I want to add an orber by User,Cont_Year line also.

Its again giving error.

And can you please tell me why you declared the custom fields like this?

Thanks & Regards

Re: Field not found

jagan — Mon, 22 Sep 2014 07:00:38 GMT

Hi,

Try like this

tab1:
LOAD
[Date of contract],
[Type of contract],
User,
rowno() as Sino
FROM

(ooxml, embedded labels, table is Лист1);

tab2_Temp:

LOAD

if([Test]=previous([Test]),'Old User','New User') as User_Status;

LOAD

[Cont_Year]&''&[Type of contract]&''&User as Test;

load
User,

[Type of contract],
Year([Date of contract]) as [Cont_Year]
resident tab1;

tab2:

Noconcatenate

LOAD

FROM tab2_Temp

ORDER BY User,Cont_Year;

DROP TABLE tab2_Temp;

Regards,

Jagan.

Re: Field not found

jyothish8807 — Mon, 22 Sep 2014 07:07:37 GMT

Hi Jagan,

Its not getting ordered.

Regards

Re: Field not found

Mon, 22 Sep 2014 07:09:20 GMT

Jyothish Kc The reason why you are getting that error is since qlikivew can only change the aliases only at run time. to resolve your use original name instead of aliases name and i strongly believe it should fix your issue.

Re: Field not found

jagan — Mon, 22 Sep 2014 07:12:41 GMT

tab1:
LOAD
[Date of contract],
[Type of contract],
User,
rowno() as Sino
FROM

(ooxml, embedded labels, table is Лист1);

tab2:

LOAD

if([Test]=previous([Test]),'Old User','New User') as User_Status;

LOAD

[Cont_Year]&''&[Type of contract]&''&User as Test;

load
User,

[Type of contract],
Year([Date of contract]) as [Cont_Year]
resident tab1

ORDER BY User, [Date of contract];

Where you are trying to sort? In script or in charts?

Regards,

Jagan.

Re: Field not found

jyothish8807 — Mon, 22 Sep 2014 07:26:13 GMT

In the script jagan, so that i can compare my rows using previous function and get an output.

Regards

Re: Field not found

jagan — Mon, 22 Sep 2014 07:28:08 GMT

Hi,

Is that above script working? I used order by [Date of Contract] which is same as Year.

Regards,

Jagan.

Re: Re: Field not found

jyothish8807 — Mon, 22 Sep 2014 07:32:42 GMT

No Jagan, its not getting ordered.

PFA

Regards

Re: Field not found

jagan — Mon, 22 Sep 2014 07:44:59 GMT

Hi,

Attach some sample data and your expected output.

Regards,

Jagan.

Re: Re: Field not found

jyothish8807 — Mon, 22 Sep 2014 07:50:59 GMT

Hi Jagan,

Its fixed now, thanks alot for your help.The above script worked with small changes.

Can you please tell me why you break the load statment into three parts.I am still learning all the stuff.

Regards

Re: Field not found

jagan — Mon, 22 Sep 2014 07:58:10 GMT

Hi,

LOAD

if([Test]=previous([Test]),'Old User','New User') as User_Status;

LOAD

[Cont_Year]&''&[Type of contract]&''&User as Test;

load
User,

[Type of contract],
Year([Date of contract]) as [Cont_Year]
resident tab1

ORDER BY User, [Date of contract];

The above method is called Preceding Load, from top to bottom in Level 1 we calculated year and the same field we need to calculated Test, instead of calculating again we are reusing the field Cont_Year in layer 2, the same in layer 3.

Hope this helps you.

Regards,

Jagan.

Re: Field not found

jyothish8807 — Mon, 22 Sep 2014 08:24:34 GMT

Thanks a lot jagan mohan, today i learnt a new thing.

Regards