topic Re: Please help on data model . Not sure what to do get needed result in QlikView

Please help on data model . Not sure what to do get needed result

prahlad_infy — Wed, 25 Nov 2020 16:16:04 GMT

Hello Sirs ,

Please tell me what , whethere i am going with wrong data model to achieve below output . I am not surly on how to getting output in below format without merging two table into single table in data model .

Q1,Q2,Q3,Q4 - values are based on the counting of LOG_ID which have REMARKS = G .

Monthly TARGET * 3 decides , if the count of Q1,Q2,Q3,Q4 are meeting qtrly target .

But I am gotting

Please Help

Thanks you .

Re: Please help on data model . Not sure what to do get needed result

Anil_Babu_Samineni — Tue, 06 Nov 2018 04:34:02 GMT

What numbers you want to see?

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Tue, 06 Nov 2018 05:37:11 GMT

Sir ,
I want a straight table , with COMPANY,EMPLOYEE,VENDOR,SELLOR from both tables .
There are following scenarios

1) COMPANY,EMPLOYEE,VENDOR,SELLOR combination present in both
2) COMPANY,EMPLOYEE,VENDOR,SELLOR combination only present in table 1 and not in table 2

3) COMPANY,EMPLOYEE,VENDOR,SELLOR combination only present in table 2 and not in table 1

so i want all these combination in one table

COMPANY,EMPLOYEE,VENDOR,SELLOR combination of table 2 where target is present are records which are important records , hence there ACTION = IMPORTANT

Numbers i wants is count of distinct log_id for all quarter , if for particular combination log_id is not present then for any quarter , it should be 0 .

if target (which is monthly) is 2 for a , COMPANY,EMPLOYEE,VENDOR,SELLOR combination , then their quarterly target will be 2*3 = 6 . SO if a combination has 6 or more distinct log_id where REMARKS are G , then for that quarter , target is achieved .

I am new to BI , this will help me learn new .

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Tue, 06 Nov 2018 05:43:53 GMT

s‌ir you helped me last time stalwar1 , that is reason i am adding you

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Tue, 06 Nov 2018 10:38:15 GMT

tresesco‌ . Help Please

Re: Please help on data model . Not sure what to do get needed result

sunny_talwar — Tue, 06 Nov 2018 11:59:15 GMT

You can call me Sunny. Can you explain (from the image) what is not right in the output you are getting? Are there specific numbers which are wrong? I am having a difficult time comparing between the two images and understanding what is not right in your current output?

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Tue, 06 Nov 2018 12:25:12 GMT

Thank you Sunny Sir for the replying .

Actually i wanted all the combinations of COMPANY EMPLOYEE VENDOR SELLOR from both the table , which is currently not working with the data model i create . That is 1st problm i think .

Secondly the style of the output , wheere all 4 quarters are coming , but i am not getting that .

Attaching excel for the easy ereference .

Thank you

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Tue, 06 Nov 2018 12:57:47 GMT

Sunny Sir ,

Please let me know , whether i have clarified my details .

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Tue, 06 Nov 2018 14:56:51 GMT

In Table 2

Corresponding Data in Table 1

Hence Result (Ignore Remark = B) while counting id for quarters

ACTION = Important because A P1 C1 S1 exists in both table .

Target not met is because monthly target is 1 for below record (see table2) , hence for a quarter their quartely target will be 1*3 = 3

and since in all quarters values (2,0,0,0) are less than 3 .

Next Scenario

Table 2

Table 1

A P1 C2 S1

A P1 C6 S1 ,

Both are present in Table 1 but not in table 2, hence they are not important and result is

stalwar1‌ .. hope this helps in clarification

Re: Please help on data model . Not sure what to do get needed result

Anonymous — Tue, 06 Nov 2018 21:23:20 GMT

Are you trying to derive Q3 is achieved in the below scenario?

Re: Please help on data model . Not sure what to do get needed result

Anil_Babu_Samineni — Wed, 07 Nov 2018 03:20:58 GMT

If you can simplify with small volume of data, It would help to understand your partial data model instead full? How you say?

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Wed, 07 Nov 2018 10:44:04 GMT

stalwar1‌ ... Please let me know , if iIam making myself clear or this cannot be achieved

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Wed, 07 Nov 2018 10:50:41 GMT

Sir .. thank you for replying .

I tried explaining with screenshot to Sunny . Can you please let me know what I am failing explain with example from your side ...

Sir .. i have shared an attached xl sheet with tables and sample output ..

One table maintains target another table is of transactional nature ...

Re: Please help on data model . Not sure what to do get needed result

Anil_Babu_Samineni — Wed, 07 Nov 2018 12:16:04 GMT

Excel is fine, What output you are expecting?

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Thu, 08 Nov 2018 03:13:45 GMT

BELOW IS SAMPLE SMALL DATA AND EXP OUTPUT. I HIGHLIGHTED FOR EASY REFERENCE .

Table2

Table 1

OUTPUTE

Re: Please help on data model . Not sure what to do get needed result

Anil_Babu_Samineni — Fri, 09 Nov 2018 10:16:38 GMT

Now explain how Quarterly Target calculated?

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Fri, 09 Nov 2018 10:23:52 GMT

Let say for a record Monthly Target is 2
then for that record quarterly target will be 2 * 3 = 6
6 is quarterly target for that record

Re: Please help on data model . Not sure what to do get needed result

prahlad_infy — Wed, 14 Nov 2018 04:59:10 GMT

Any solution sir ?