topic Re: Hello Everyone,Stucked on a task. in QlikView

Hello Everyone,Stucked on a task.

Thu, 07 Nov 2013 08:07:59 GMT

Hello,

I want to use scripting like as

data:

LOAD

PlannedProduction as PP,

PlannedRecieves as PR,

PreviousStockonHand,

PreviousStockonHand+PlannedProduction+PlannedRecieves-Forecast as ProjectedStockOnHand

Resident Table where PeriodId='1' ;

store data into E:\Poornima\forecastAnalysis\data.qvd(qvd);

drop Table data;

Table:

LOAD PP,

PR,

PreviousStockonHand,

ProjectedStockOnHand

FROM

data.qvd

(qvd);

Concatenate

LOAD

PlannedProduction as PP,

PlannedRecieves as PR,

ProjectedStockOnHand as PreviousStockonHand,

PreviousStockonHand+PlannedProduction+PlannedRecieves-Forecast as ProjectedStockOnHand

Resident Table where PeriodId='2' ;

where i want to store the ProjectedStockOnHand for period 1 in PreviousStockonHand for period 2 .. and then concatenate both the tables.

Showing Problem, is there any way to solve it?

Thanks in Advance and Regards.

Re: Hello Everyone,Stucked on a task.

israrkhan — Thu, 07 Nov 2013 08:26:51 GMT

Hi,

What error you are getting?

1)if you your PeriodId is integer, the try without ' ' like PeriodId=2 ;

2) and make sure your all column PreviousStockonHand+PlannedProduction+PlannedRecieves-Forecast as ProjectedStockOnHand are in numbers format not text,

or try to convert add the like

(PreviousStockonHand+PlannedProduction+PlannedRecieves) - (Forecast) as ProjectedStockOnHand

Re: Hello Everyone,Stucked on a task.

Thu, 07 Nov 2013 10:06:03 GMT

Hello Israr,

Thanks for reply ,

But the problem is inside the logic,

which i am trying to include in this script.

Please read the above script and question carefully and then answer me.

Re: Hello Everyone,Stucked on a task.

qliksus — Thu, 07 Nov 2013 10:13:25 GMT

Hi,

Can you explain the error? Means what type of error.

Re: Hello Everyone,Stucked on a task.

Thu, 07 Nov 2013 10:17:55 GMT

This is the error,

But please go through my purpose of doing this from previous statement.

Field not found - <ProjectedStockOnHand>

Concatenate

LOAD

PlannedProduction as PP,

PlannedRecieves as PR,

ProjectedStockOnHand as PreviousStockonHand,

(PreviousStockonHand+PlannedProduction+PlannedRecieves)-Forecast as ProjectedStockOnHand

Resident Table where PeriodId='2'

Re: Re: Hello Everyone,Stucked on a task.

israrkhan — Thu, 07 Nov 2013 10:19:19 GMT

data:

LOAD

PlannedProduction as PP,

PlannedRecieves as PR,

PreviousStockonHand,

PreviousStockonHand+PlannedProduction+PlannedRecieves-Forecast as ProjectedStockOnHand

Resident Table where PeriodId='1' ; // here You loaded Data only for ProductID =1

store data into E:\Poornima\forecastAnalysis\data.qvd(qvd); // Data.qvd only have 1 record for ProductID = 1

drop Table data;// Dont drop it here

Table:

LOAD PP,

PR,

PreviousStockonHand,

ProjectedStockOnHand

FROM data.qvd(qvd); // You loaded whole all data from data.qvd into Table, but this qvd has only one record for productID =1

Concatenate

LOAD

PlannedProduction as PP,

PlannedRecieves as PR,

ProjectedStockOnHand as PreviousStockonHand,

PreviousStockonHand+PlannedProduction+PlannedRecieves-Forecast as ProjectedStockOnHand

Resident Table where PeriodId='2' ; // This table does not have any record where ProductID= 2, it has only one record for productid=1

Solution:

1) dont drop table Data after first load, drop it at the end of script.

2) LOAD last table from Resident Data, not Resident Table (Last line of your script..)

... hope it help you...

Re: Hello Everyone,Stucked on a task.

Thu, 07 Nov 2013 10:31:27 GMT

Hii Israr,

That is not productID that is periodId which stores the data for period 1.

now using the data for period 1 i need to calculate for period 2..

Re: Hello Everyone,Stucked on a task.

israrkhan — Thu, 07 Nov 2013 10:34:33 GMT

i meant your field, PeriodId

have you go through with my above post, was it your error...

if you did not solve yet, post a sample ....

Re: Hello Everyone,Stucked on a task.

sasikanth — Thu, 07 Nov 2013 11:53:14 GMT

HI Nupur

1)While loading source table itself calculate 'projectedstockonhand'

2)then store soure table into QVD;

Table:

LOAD Pid,

Forecast,

PlannedProduction as PP,

PlannedRecieves as PR,

PreviousStockonHand,

PreviousStockonHand+PlannedProduction+PlannedRecieves-Forecast as ProjectedStockOnHand

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

store Table into E:\data1.qvd(qvd);

DROP Table Table;

tab1:

LOAD

PP,

PR,

previousstockonhand

projectedstockonhand From E:\data1.qvd(qvd) Where pid=1;

Concatenate

LOAD

pp,

pr,

projectedstockonhand as previousstockonhand,

pp+pr+previousstockonhand as projected From E:\data1.qvd(qvd) Where pid=2;

Hope thsi may help you to solve

Re: Hello Everyone,Stucked on a task.

Thu, 07 Nov 2013 11:54:37 GMT

Hii Israr ,

Not able to solve.

I am doing following scripting in my qwv file.

It is showing me invalid expression error.

LOAD

ProductId,

PeriodId,

sum(PlannedProduction) as PP,

sum(PlannedRecieves) as PR,

sum(PreviousStockonHand),

(PreviousStockonHand+PlannedProduction+PlannedRecieves)-Forecast as ProjectedStockOnHand

Resident Table where PeriodId='1' Group by ProductId ;

Re: Hello Everyone,Stucked on a task.

israrkhan — Thu, 07 Nov 2013 12:00:03 GMT

REMEMBER, If you are using aggregate function in load or selct , then you have to group by all fields in load or select statment.

LOAD

ProductId,

PeriodId,

sum(PlannedProduction) as PP,

sum(PlannedRecieves) as PR,

sum(PreviousStockonHand),

(PreviousStockonHand+PlannedProduction+PlannedRecieves)-Forecast as ProjectedStockOnHand

Resident Table where PeriodId='1'

Group by ProductId,PeriodId,PlannedProduction,PlannedRecieves,PreviousStockonHand ;