topic Re: Urgent Help in QlikView

Urgent Help

gaurav2017 — Fri, 20 Nov 2020 18:02:30 GMT

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

Re: Urgent Help

Tue, 19 Jun 2012 04:47:45 GMT

Hi Gaurav,

1. You just need to load 3 excel files

2. Create a straight table with remaining stock dimension.

View attached example.

Hope it helps

Thanks,

Supriya

Re: Urgent Help

gaurav2017 — Tue, 19 Jun 2012 05:29:27 GMT

Hi Supriya,

I am using Personal Edition, so I can't open qvw you attached. Can you Pls share code.

Re: Urgent Help

gaurav2017 — Tue, 19 Jun 2012 05:31:44 GMT

Also I want to calculate "RemainingStock" at the backend in the script.

Re: Urgent Help

Tue, 19 Jun 2012 05:50:12 GMT

Hi Gaurav,

LOAD prod,

quan,

open as o

FROM

C:\Users\supriya_thigale\Desktop\Book1.xlsx

(ooxml, embedded labels);

LOAD prod,

quan,

prim as p

FROM

C:\Users\supriya_thigale\Desktop\Book2.xlsx

(ooxml, embedded labels);

LOAD prod,

quan,

sec

FROM

C:\Users\supriya_thigale\Desktop\Book3.xlsx

(ooxml, embedded labels);

Then i have created a straight table with remaining stock as dimension.

Thanks

Supriya

Re: Urgent Help

gaurav2017 — Tue, 19 Jun 2012 05:54:02 GMT

What you wrote in the expression field in your straight table ??

Did you use "Calculated Dimension" in the Dimension Tab ??

Re: Urgent Help

Tue, 19 Jun 2012 05:59:19 GMT

I wote

=(OpeningStock + PrimarySales) - SecondarySales

and labelled it as RemainingStock..

It worked very fine

Re: Urgent Help

Tue, 19 Jun 2012 06:01:41 GMT

You can use expression and calculated dimension both.. Both work fine

Re: Urgent Help

Tue, 19 Jun 2012 06:33:41 GMT

Hi Gaurav,

Try following code:

temp:

LOAD ProductName,

Quantity,

OpeningStock

FROM

C:\Users\Desktop\Book1.xlsx

(ooxml, embedded labels);

INNER JOIN

LOAD ProductName,

Quantity,

PrimarySales

FROM

C:\Users\Desktop\Book2.xlsx

(ooxml, embedded labels);

INNER JOIN

LOAD ProductName,

Quantity,

SecondorySales

FROM

C:\Users\Desktop\Book3.xlsx

(ooxml, embedded labels);

data:

LOAD *,

(OpeningStock + PrimarySales) - SecondarySales as RemainingStock

RESIDENT temp;

DROP TABLE temp;

Hope this helps.

BR, Neha

Re: Urgent Help

gaurav2017 — Tue, 19 Jun 2012 06:43:15 GMT

Thanks Supriya & Neharangari. Sorry, I never mentioned that all 3 tables are crosstables. I guess 'Join' don't work in crosstables.

Re: Urgent Help

Anonymous — Tue, 19 Jun 2012 07:45:19 GMT

hi gaurav

Can u atlst provide screen shots ?

Re: Urgent Help

gaurav2017 — Tue, 19 Jun 2012 08:40:19 GMT

Screenshot of what ???

1. Application developed

2. Excel files

3. Code I wrote.

Re: Urgent Help

Anonymous — Tue, 19 Jun 2012 09:19:36 GMT

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

Re: Urgent Help

jagannalla — Tue, 19 Jun 2012 11:11:09 GMT

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;

Re: Urgent Help

Anonymous — Wed, 20 Jun 2012 05:19:57 GMT

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.

Re: Urgent Help

gaurav2017 — Wed, 20 Jun 2012 06:11:39 GMT

Re: Urgent Help

Anonymous — Wed, 20 Jun 2012 06:34:01 GMT

hi gaurav

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

regards,

vaibhav.

Re: Urgent Help

gaurav2017 — Wed, 20 Jun 2012 06:39:40 GMT

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

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

Thanks & Regards

Re: Urgent Help

jagannalla — Wed, 20 Jun 2012 08:15:22 GMT

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.

Re: Urgent Help

gaurav2017 — Wed, 20 Jun 2012 11:22:59 GMT

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