-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 17, 2017 7:24 AM (in response to Agrim Sharma)1) Do you really need to create one table for each of your Excel tab? I would suggest to create a loop to load M1 to M12 and then may be add flag for each tab
2) Left Join Value, I/E, Dept, Location & Site to your FinalTable based on the key field. Can you share that Excel again?
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 19, 2017 10:42 PM (in response to Sunny Talwar )Hi sunny,
thanks for reply but Sunny i cant understand how to do it?
can you please send me by editing script?
and i agree with your suggestion- 'I would suggest to create a loop to load M1 to M12 and then may be add flag for each tab'
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 19, 2017 10:46 PM (in response to Sunny Talwar )Sunny,
this is my Dashboard and Excel file for your reference.
-
RESOURCE PLANNING DASHBOARD.qvf 256.0 K
-
Resource Planning v1.2.xlsx 195.4 K
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 20, 2017 8:18 PM (in response to Agrim Sharma)Check this script
MappingTable1:
Mapping
LOAD ROLE ,
[REF BUILD] & '|' & Enable & '|' & RUN
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is ROLES);
Temp:
CrossTable (ROLE, Value,5)
LOAD [Employee Name],
[Location],
[Site],
[Dept],
[I/E],
[Business Relationship (BRM)],
[Architect / PM],
[Analyst],
[Admin],
[Support & SME],
[Project Integrator],
[Specialist],
[Technician],
[Manager / Team Leader],
[Enable]
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is [Emp vs Role]);
MappingTable2:
Mapping
LOAD [Employee Name]&ROLE,
Value
Resident Temp;
DROP Table Temp;
Temp2:
CrossTable (Team, Value2)
LOAD [Employee Name],
[Alignment & Direction],
Performance,
[Sales, Marketing, and Logistics],
[Industrial, Quality, and Research],
[Finance, Purchasing, HR, & IT],
[Web Application Systems],
[Content Collaboration],
[Business Intelligence],
[B2B / EDI],
[Data Mart Management],
Testing,
[Innovation Center],
[Engineering and Architecture],
[Infrastructure and Operations],
OCM,
[Security and Risk],
[Client Services],
[Access Rights],
Symphony
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is [Emp vs Team]);
MappingTable3:
Mapping
LOAD [Employee Name]&Team,
Value2
Resident Temp2;
DROP Table Temp2;
Temp3:
CrossTable(Month, Value3)
LOAD [Employee Name],
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is [Actual Working Days]);
MappingTable4:
Mapping
LOAD [Employee Name]&Month(Date#(Month, 'MMM')),
Value3
Resident Temp3;
DROP Table Temp3;
FOR i = 1 to 12
Table:
LOAD RowNo() as RowNo,
Month,
[Employee Name],
ROLE,
Team,
[TOTAL WORKING DAYS],
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) * ApplyMap('MappingTable4', [Employee Name]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD],
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) * ApplyMap('MappingTable4', [Employee Name]&Month(Date#(Month, 'MMMM')), 0) as Enable,
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 3) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) * ApplyMap('MappingTable4', [Employee Name]&Month(Date#(Month, 'MMMM')), 0) as RUN,
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) as [REF BUILD1],
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) as Enable1,
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 3) * ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) * ApplyMap('MappingTable3', [Employee Name]&Team, 0) as RUN1
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is M$(i));
Next
Left Join (Table)
LOAD [Employee Name],
[Location],
[Site],
[Dept],
[I/E]
FROM [lib://Lib/Resource Planning v1.2.xlsx]
(ooxml, embedded labels, table is [Emp vs Role]);
-
RESOURCE PLANNING DASHBOARD.qvf 230.0 K
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 1:17 AM (in response to Sunny Talwar )Hi Sunny,
i got a situation in which, i want to get conditional filter wise sum.
that means,suppose when I select ROLE(filter)-Architect it should give me sum of "Values" of ROLE only=42.86(that's calculating correct in my dashboard),
but along with it, when i select TEAM(filter)-finance=9.55(that's also calculating correct in my dashboard),
but along with it,
when i select filter of TEAM(Industrial). give me sum as 14.70(this value is calculating incorrectly as well as showing incorrect count of no. employee) that should be 3.10.
for your reference see this excel file,
apply filter on ROLE-Architect OUTPUT=42.86(SUM(VALUE of ROLE))
apply filter on TEAM-finance=(VALUE of ROLE)*(VALUE of TEAM for each employee)=9.55
and apply filter on TEAM-Industrial=(VALUE of ROLE)*((VALUE of TEAM(finance)+(VALUE of TEAM(Industrial)) for each employee)=3.33
5 Names of Employee
and if you check sum of TEAM(Industrial and finance) will be 3.33
this "5" i want as No. of Employee & head count as 3.33 in Dashboard.
-
Resource Planning v1.2.xlsx 214.8 K
-
DASHBOARDTEST.QVF 224.0 K
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 22, 2017 4:36 AM (in response to Agrim Sharma)-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 4:45 AM (in response to Sunny Talwar )just replace them with #1KPI you will visualize it, i am looking for No. od Employee and FTE
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 22, 2017 5:00 AM (in response to Agrim Sharma)I am not sure I follow
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 5:09 AM (in response to Sunny Talwar )now you can see visuals
-
DASHBOARDTEST.QVF 240.0 K
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 22, 2017 6:50 AM (in response to Agrim Sharma)Checking
-
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 5:50 AM (in response to Sunny Talwar )Sunny,
did you got my point?
-
-
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 5:05 AM (in response to Sunny Talwar )Sunny,
I think its matrix calculation eg.
apply filter on ROLE-Architect FTE=42.86(SUM(VALUE of ROLE)), No. of Emp. =45(correct)
apply filter on TEAM-finance=(VALUE of ROLE)*(VALUE of TEAM finance=9.55, No. of Emp. =17(correct)
and apply filter on TEAM-Industrial=(VALUE of ROLE)*((VALUE of TEAM(finance)+(VALUE of TEAM(Industrial)) for each employee)=3.10
again, when i select an ROLE-Analyst than output should be((VALUE of Architect) + (VALUE of Analyst ))*((VALUE of TEAM(finance)+(VALUE of TEAM(Industrial))
No. of employee=1
FTE=0.998
-
-
-
-
-
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 22, 2017 6:59 AM (in response to Agrim Sharma)I think since you are doing the calculation in the script, you are loosing the ability to sum team on the front end. Is there a reason you want to do this in the script? Can we move this to the front end?
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 7:05 AM (in response to Sunny Talwar )no there is no reason of doing it in script, yes we can do it in front end but i am not getting desired output as i mentioned earlier
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 7:06 AM (in response to Agrim Sharma)by doing it in front end will i get matrix calculation? of ROLE & TEAM like Follows:-
apply filter on ROLE-Architect FTE=42.86(SUM(VALUE of ROLE)), No. of Emp. =45(correct)
apply filter on TEAM-finance=(VALUE of ROLE)*(VALUE of TEAM finance=9.55, No. of Emp. =17(correct)
and apply filter on TEAM-Industrial=(VALUE of ROLE)*((VALUE of TEAM(finance)+(VALUE of TEAM(Industrial)) for each employee)=3.10
again, when i select an ROLE-Analyst than output should be((VALUE of Architect) + (VALUE of Analyst ))*((VALUE of TEAM(finance)+(VALUE of TEAM(Industrial))
No. of employee=1
FTE=0.998
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 22, 2017 7:11 AM (in response to Agrim Sharma)Can you make a change like this in your script and rerun it for me?
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) as [REF BUILD1],
ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) as [REF BUILD2],
ApplyMap('MappingTable3', [Employee Name]&Team, 0) as [REF BUILD3],
ApplyMap('MappingTable4', [Employee Name]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD4],
SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) as Enable1,
ApplyMap('MappingTable2', [Employee Name]&ROLE, 0) as Enable2,
ApplyMap('MappingTable3', [Employee Name]&Team, 0) as Enable3,
ApplyMap('MappingTable4', [Employee Name]&Month(Date#(Month, 'MMMM')), 0) as Enable4,
Essentially create a new field instead of multiplying them into 1. Do it for Enable, REF BUILD and Run
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 7:15 AM (in response to Sunny Talwar )m really sorry sunny i didnt understand
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 22, 2017 7:17 AM (in response to Agrim Sharma)Can you reload the attached application for me
-
DASHBOARDTEST.QVF 240.0 K
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 7:29 AM (in response to Sunny Talwar )i did reload but its not showing correct values and graph also got changed
-
DASHBOARDTEST.QVF 240.0 K
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 22, 2017 7:31 AM (in response to Agrim Sharma)Will check it in a bit. Incorrect numbers is okay
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 9:10 AM (in response to Sunny Talwar )Ok Sunny
Thanks,
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 22, 2017 9:12 AM (in response to Agrim Sharma)Its complex... I am looking at it while doing my own work... not sure if I will get to the bottom of it, but I am trying
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 22, 2017 10:37 AM (in response to Sunny Talwar )its okay, thanks a lot for giving your timei will wait..
mean while please guide me bit, how can i learn qlik language, any book? or Course?
can you suggest something
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 23, 2017 5:25 AM (in response to Sunny Talwar )hi Sunny,
sorry to disturb you again.
did you found some time for this problem
Thanks,
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 23, 2017 5:28 AM (in response to Agrim Sharma)I did find a lot of time, but I still didn't find a solution. I will have to go back to Excel to decipher this. I need more time
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 23, 2017 6:01 AM (in response to Sunny Talwar )its ok Thanks a lot,
i have attached all the attachments earlier and a reference file to cross check the calculations.
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 26, 2017 11:36 PM (in response to Sunny Talwar )Hi Sunny,
hope you fine:)
did you found anything on this topic?
can you please suggest me something like, is this calculation is possible or not?
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 28, 2017 2:32 AM (in response to Sunny Talwar )hello Sunny,
how are you:)
can you please suggest me that this calculation is possible or not
-
Re: how to use fields if table is dropped because of mapping
Sunny Talwar Feb 28, 2017 9:45 AM (in response to Agrim Sharma)Checking now
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Feb 28, 2017 10:42 PM (in response to Sunny Talwar )thanks Sunny, i will wait for your kind response
-
Re: how to use fields if table is dropped because of mapping
Agrim Sharma Mar 2, 2017 3:51 AM (in response to Sunny Talwar )Hi Sunny,
sorry to disturb you again please help me i tried many things but no one worked.
-
-
-
-
-
-
-
-
-
-
-
-
-
-