Urgent Help

[gaurav2017]

Hi there,

I am having 3 excel files

having fields:

ProductName, Quantity, OpeningStock

ProductName, Quantity, PrimarySales

ProductName, Quantity, SecondarySales

resp.

I have to load these 3 files in qlikview & then calculate a new field called "RemainingStock",

which is a calculated field.

RemainingStock = (OpeningStock + PrimarySales) - SecondarySales

How can it be done.

Regards,

Gaurav Malhotra

[Anonymous]

hi gaurav

                 Can u atlst provide screen shots ?

[gaurav2017]

Screenshot of what ???

1. Application developed

2. Excel files

3. Code I wrote.

[Anonymous]

since u have personal edition

provide screen shots for code u have or jst attach the excel file from whr u r getting data for all...........

[jagannalla]

Try this,

Alias F1 as S.no., F2 as Product, F3 as Quantity;

FiscalCalendar:

LOAD * Inline

[Month, Quarter

Jan, Q4

Feb, Q4

Mar, Q4

Apr, Q1

May, Q1

Jun, Q1

Jul, Q2

Aug, Q2

Sep, Q2

Oct, Q3

Nov, Q3

Dec, Q3];

Main:

LOAD Month,OpeningStock;

CrossTable(Month,OpeningStock,1)

LOAD

F1,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec,

Jan,

Feb,

Mar

FROM

(ooxml, embedded labels, header is 3 lines, table is RJ, filters(

Remove(Row, Pos(Top, 33)),

Remove(Row, Pos(Top, 32)),

Remove(Row, Pos(Top, 28)),

Remove(Row, Pos(Top, 24)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 6)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 18)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 1))

));

Left Join

LOAD Month,PrimarySales;

CrossTable(Month,PrimarySales,1)

LOAD

F2,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec,

Jan,

Feb,

Mar

FROM

(ooxml, embedded labels, header is 3 lines, table is RJ, filters(

Remove(Row, Pos(Top, 33)),

Remove(Row, Pos(Top, 32)),

Remove(Row, Pos(Top, 28)),

Remove(Row, Pos(Top, 24)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 6)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 18)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 1))

));

Left Join

LOAD Month,SecondarySales;

CrossTable(Month,SecondarySales,1)

LOAD

F3,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec,

Jan,

Feb,

Mar

FROM

(ooxml, embedded labels, header is 3 lines, table is [RJ (Secondary)], filters(

Remove(Row, Pos(Top, 33)),

Remove(Row, Pos(Top, 32)),

Remove(Row, Pos(Top, 28)),

Remove(Row, Pos(Top, 24)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 6)),

Remove(Col, Pos(Top, 21)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 18)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 1))

));

Res_Temp:

LOAD Month,(OpeningStock + PrimarySales) - SecondarySales as RemainingStock  Resident Temp;

[Anonymous]

hi gaurav,

        the code provided by jagan is correct........jst dont include load statement....

look below given code:

CrossTable

(Month, Opening)

LOAD F1,

     Apr,

     May,

     Jun,

     Jul,

     Aug,

     Sep,

     Oct,

     Nov,

     Dec,

     Jan,

     Feb,

     Mar

FROM



(ooxml, embedded labels, header is 3 lines, table is RJ, filters(

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 1)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 10)),

Remove(Row, Pos(Top, 14)),

Remove(Row, Pos(Top, 17)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 23)),

Remove(Row, Pos(Top, 26)),

Remove(Row, Pos(Top, 26)),

Remove(Col, Pos(Top, 15)),

Remove(Col, Pos(Top, 14)),

Remove(Col, Pos(Top, 15)),

Remove(Col, Pos(Top, 14))

));


i hope u can undrstand how to load excel wit help of cross table as given above.

[gaurav2017]

[Anonymous]

hi gaurav

                 Dont apply any joins..........jst load the cross tables tatz it..........

regards,

vaibhav.

[gaurav2017]

I also renamed "Main" (table name) in the code to "Temp".

but It doesn't work & show error like below:

Thanks & Regards

[jagannalla]

Gaurav,

I want to expalin you few points.

1. We are trying to develop a expression in script i.e. (OpeningStock + PrimarySales) - SecondarySales . Here we are calculating the expression on fields which are not there in in single table. Each field is coming from different tables. So we need to make single table to calculate this expression.

2. To make a single table we need to use Joins concept. If we don't use joins the qvw loads the three different tables seperately and links the tables with help of common field key.

So what i did here is first i'll load cross table of OpeningStock and in resident table(i.e. Main table) i'll hold the month and values of opening stock. Now i'll load crosstable of PrimarySales and i'll join the resident table of primarysales with Main table. Say thing for SecondarySales table.

I forgot to tell you i'm also droping the tables of OpeningStock, PrimarySales, SecondarySales. B'coz we have this fields in main table. I tried with sample inline data please check for your reference.

Alias F1 as S.no., F2 as Product, F3 as Quantity;

FiscalCalendar:

LOAD * Inline

[Month, Quarter

Jan, Q4

Feb, Q4

Mar, Q4

Apr, Q1

May, Q1

Jun, Q1

Jul, Q2

Aug, Q2

Sep, Q2

Oct, Q3

Nov, Q3

Dec, Q3];

OpeningStock:

CrossTable(Month,OpeningStock,1)

LOAD * Inline

[F1,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar

100,200,300,400,500,600,100,200,300,400,500,600,1000];

Main:

LOAD Month,OpeningStock Resident OpeningStock;

DROP Table OpeningStock;

PrimarySales:

CrossTable(Month,PrimarySales,1)

LOAD * Inline

[F1,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar

100,200,300,400,500,600,100,200,300,400,500,600,1000];

Left Join (Main)

LOAD Month,PrimarySales Resident PrimarySales;

DROP Table PrimarySales;

SecondarySales:

CrossTable(Month,SecondarySales,1)

LOAD * Inline

[F1,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar

10,20,30,40,50,60,10,20,30,40,50,60,10];

Left Join (Main)

LOAD Month,SecondarySales Resident SecondarySales;

DROP Table SecondarySales;

Resident_Main:

LOAD Month,(OpeningStock + PrimarySales) - SecondarySales as RemainingStock  Resident Main;

Cheers!

Hope it helps you.

[gaurav2017]

I am very thankful to all of you for replying to my post. Thanks a ton.

Every reply is meaningful to me.

It is stucked at the reload. What to do now ???

Should I wait or abort ??