Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Hello Everyone,Stucked on a task.

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.

10 Replies
israrkhan
Specialist II
Specialist II

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

Not applicable
Author

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.

qliksus
Specialist II
Specialist II

Hi,

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

Not applicable
Author

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'

israrkhan
Specialist II
Specialist II

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

Not applicable
Author

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

israrkhan
Specialist II
Specialist II

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

sasikanth
Master
Master

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

Not applicable
Author

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 ;