topic Re: Can any one help to Solve my problem in QlikView

Can any one help to Solve my problem

Anonymous — Mon, 15 Sep 2014 05:25:01 GMT

Hi i am getting 3 sources in which has to be combined and in all excels almost all measures are common i concatenated all the 3 excels sources .in script i need to calculate measure

Re: Can any one help to Solve my problem

Mon, 15 Sep 2014 05:31:51 GMT

if all fields are common in all three sheets. then qlikview automatically concatenate all tables in first loaded table.

Re: Can any one help to Solve my problem

Anonymous — Mon, 15 Sep 2014 05:37:35 GMT

What is your actual requirement...

Directory;

LOAD *,'Model' as FLAG

FROM

[My Data Struggle.xlsx]

(ooxml, embedded labels, header is 2 lines, table is Model);

concatenate

LOAD *,'Sales' as FLAG

FROM

[My Data Struggle.xlsx]

(ooxml, embedded labels, header is 2 lines, table is Sales);

concatenate

LOAD *,'Loss' as FLAG

FROM

[My Data Struggle.xlsx]

(ooxml, embedded labels, header is 2 lines, table is Loss);

Re: Can any one help to Solve my problem

Mon, 15 Sep 2014 05:50:16 GMT

Your concatenation is fine. Try these set analysis to calculate your measures

For Sales you do, sum({$<Status='MA' or Status = 'TH'>} Amount),

For Income, sum({$<Status='KA' or Status = 'SR'>} Amount),

For Purchase, sum({$<Status='ASD' or Status = 'BSD'>} Amount),

For Net Loss, sum({$<Status='FD'>} Amount),

For Gross Loss, sum({$<Status='MA' or Status = 'TH', ModelGross='ABS' or ModelGross= 'RET' >} Amount)

Hope this helps.

Thanks

Suraj

Re: Can any one help to Solve my problem

Anonymous — Mon, 15 Sep 2014 08:35:04 GMT

Thanx for your solutions I need to get all data at script level.First loading one data source used where Match(Status,'MA','TH'); and used group by with dimension for all measures separately in script and generated qvd separetly,synthatic keys are forming i.e,Need all script in temp like table where calculations are to be done not changing sources.In detail i have another source where almost all data fields are same ther is no primary key but at final both are to be compared side by side.

the dimensions in Model,sales,Loss.xlx all are checked in pivot table.

Dimensions:Order No

Pick Slip No

CustId

Source

Minor

Class

Model.xlx Measures:Income

Sales

Sales.xlx Measure: Puchase

Ledger

Loss.xlx Measure: Net Loss

Gross loss

Amount Values act like a hyperlink on clicking it chould populate pivot table with default dimension and matching should show Amount at lowest drill downs.

2)To show Welcome tab first scrren with current user login Name

All measure are to be displayed

Re: Can any one help to Solve my problem

Anonymous — Mon, 15 Sep 2014 08:42:32 GMT

the dimensions in Model,sales,Loss.xlx all are checked in pivot table.

Dimensions:Order No

Pick Slip No

CustId

Source

Minor

Class

Model.xlx Measures:Income

Sales

Sales.xlx Measure: Puchase

Ledger

Loss.xlx Measure: Net Loss

Gross loss

Amount Values act like a hyperlink on clicking it chould populate pivot table with default dimension and matching should show Amount at lowest drill downs.

2)To show Welcome tab first scrren with current user login Name

All measure are to be displayed

Re: Re: Can any one help to Solve my problem

Mon, 15 Sep 2014 17:00:18 GMT

Attached is the document. Is this something you are looking for?

Thank you

Suraj

Re: Can any one help to Solve my problem

Anonymous — Tue, 16 Sep 2014 08:41:34 GMT

Using Personal Edition workin og POC not able to open the qvw,three excel files will be source from one system.Need to calculations at script level.Loaded all excel files in separate tab i need all script level calculations for Model,sales,Loss.xls.Is it possible?After getting let say measure with sum.on clicking Amount(2345) it should popup another pivot table where the one more dimension is added i.e., Local is lowest level in drill down?Like this any possiblities in qlikview.As i'm new to qlik