topic Re: Loading Fields as variables ( Data Model) in QlikView

Loading Fields as variables ( Data Model)

vikasmahajan — Tue, 07 Jun 2016 05:16:46 GMT

Dear all,

I have 3 tables loaded using Qualify prefix , Following are my data model along with 3 tables , i loaded excel file with some fields which i want to use as a variables for displaying cost of each opportunity.

=sum(vInititiavtiveCharges)

vInititiavtiveCharges - I want to calculate each opportunity sum as per user selected in list also i have 4-5 fields as variables i want to use in my dashboard please suggest how to achieve this ?

Store each opportunity amt into Variable and show total dynamically.

Many Thanks

Vikas

Message was edited by: vikas mahajan

Re: Loading Fields as variables ( Data Model)

tresB — Tue, 07 Jun 2016 05:42:22 GMT

Hi Vikas,

What is your measure and expected output? Please explain more on expected.

Re: Loading Fields as variables ( Data Model)

vikasmahajan — Tue, 07 Jun 2016 05:44:50 GMT

Measure - > SUM(AMT)

Sum(Amt) for opportunity which user select from list box.

For Example

NonPrdBKp - > sum(Amt)

BKPPRD -> Sum(amt)

NASBKP -> Sum(AMT)

As per user selection.

Thanks

Vikas

Re: Loading Fields as variables ( Data Model)

tresB — Tue, 07 Jun 2016 05:47:28 GMT

I don't see any field called opportunity.

Re: Loading Fields as variables ( Data Model)

vikasmahajan — Tue, 07 Jun 2016 05:52:17 GMT

Initative is nothing but opportunity.

Re: Loading Fields as variables ( Data Model)

vikasmahajan — Tue, 07 Jun 2016 05:53:26 GMT

Please let me know any details you required ?

Vikas

Re: Loading Fields as variables ( Data Model)

tresB — Tue, 07 Jun 2016 05:58:44 GMT

Data model is yours and that 'nothing but...' is obvious for you but me.

And yes, I am not yet sure about your requirement. Lets take a case: you select 'Backup Deocm - Non PROD' , what is expected output (please explain the logic as well)?

Re: Loading Fields as variables ( Data Model)

vikasmahajan — Tue, 07 Jun 2016 06:06:20 GMT

Thanks Tesesco , I have made little modification's in QVW and attached again following are my requirement

If user select Backup - Non PROD from list box then in text box i want to display the total amt for (Backup - Non PROD) initiative/opportunity dynamically .

7130.24 sum of amt for Backup - Non PROD opportunity and rest accordingly as per user selections.

Thanks

Vikas

Re: Loading Fields as variables ( Data Model)

vikasmahajan — Tue, 07 Jun 2016 06:10:32 GMT

Still require more details ?

Vikas

Re: Loading Fields as variables ( Data Model)

jonathandienst — Tue, 07 Jun 2016 06:10:37 GMT

er.. and sum(Amt) does not work? In what way is it incorrect? I do not understand what the problem is.

Re: Loading Fields as variables ( Data Model)

tresB — Tue, 07 Jun 2016 06:21:59 GMT

Couple of points:

The sample data is not having all the possible associations. E.g. - No relative Cost Center in the table NPRDBKP table, so your expected output (at least for this data set) is not legitimate I guess.
You probably need to revisit and correct the data model.

Re: Loading Fields as variables ( Data Model)

sujeetsingh — Tue, 07 Jun 2016 06:30:46 GMT

As I think you can use a simple expression for the variable which changes on selection at dashboard.

Re: Loading Fields as variables ( Data Model)

vikasmahajan — Tue, 07 Jun 2016 06:32:23 GMT

Sorry i have corrected data model and attached the same now i want the monthly charges total for each initiatives as per user selection in text box

SUM( NonPrdBkp.Monthly Charges) in text box if user select non prod backup ,

Sum (BKPPRD.Monthly Charges) in text box if user select prod backup ,

Sum (NASBKP.Monthly Charges) in text box if user select NAS backup

Hope this is clear now ?

Re: Loading Fields as variables ( Data Model)

vikasmahajan — Tue, 07 Jun 2016 06:35:21 GMT

Thanks i can not hard code for 20 opportunity here in sample i have only 3 opportunity but like this i have more than 50 + opportunity to code. So i have loaded all fields into excel and by loading this excel i want to show total of each opportunity dynamically as per user selection in list box.

Vikas

Re: Loading Fields as variables ( Data Model)

tresB — Tue, 07 Jun 2016 07:05:15 GMT

I suggest to Correct the data model, may be like: Concatenate all three NAS, PROD, NPROD into one table and use a flag to distinguish them like:

Final:

Load * , 'NAS' as Flag From <NAS>;

Load * , 'PRD' as Flag From <PRD>;

Load * , 'NPRD' as Flag From <NPRD>;

And then in the front end use this flag as selection and expression like: = Sum([Monthly Charges])