Vikas Kumar Tiwari on LinkedIn: #qliksense #setexpression #vt #se3 (2024)

Hi All,❑ Please go through this post to understand a very useful function Applymap() in qlik sense , its mandatory conditions , use cases and limitations.The applymap function in Qlik Sense is a versatile function used to map values from one table to another based on a mapping table. It is particularly useful for data transformation, cleansing, and standardization tasks. Here’s an in-depth look at it :◇ Basic Syntax : applymap('map_name', field/value, default_value)➪ map_name: The name of the mapping table.➪ expression: The field/value to be mapped.➪ default_value: A value to be returned if the mapping does not find a match (optional).To use the applymap function effectively in Qlik Sense, there are several prerequisites and a key mandatory condition that must be met.◇ Prerequisites :➪ A basic understanding of Qlik Sense scripting language is essential as applymap is used within load scripts.➪ Ensure that the data sources you plan to use are properly connected and accessible within Qlik Sense.➪ You need to create a mapping table before using the applymap function. The mapping table should be defined with a mapping load statement.◇ Mandatory Condition :➪ The mapping table referenced by applymap must exist and be correctly loaded before it is used in the script. If mapping table is not found or not properly defined, applymap will not work and will result in an error.➪ The mapping table should contain exactly two columns: first column (key) and second column (value).**********************Script*****************************DepartmentMap:mapping loadDeptCode,DeptNameinline [DeptCode, DeptNameHR, Human ResourcesIT, Information TechnologyFIN, Finance];StatusMap:mapping loadStatusID,StatusNameinline [StatusID, StatusName1, Active2, Inactive];LOADEmployeeID,EmployeeName,applymap('DepartmentMap', DeptCode, 'Unknown') as Department,applymap('StatusMap', StatusID, 'Unknown') as StatusFROM [EmployeeData];*********************************************************************Here are some specific situations and examples where applymap is particularly useful:➪ When you have codes or abbreviations in your data that need to be replaced with more descriptive names, applymap is ideal. Ex: Replacing department codes with department names.➪ Using applymap to provide default values when no match is found in the mapping table. Ex: Assigning a default status 'Unknown' when the status ID or DeptCode is missing.➪ Replacing complex nested if statements with a cleaner and more efficient applymap function. Ex: Simplifying multiple conditional replacements.◇ Limitations :➪ Single key-value mapping, If you need to map based on a combination of columns, it alone won't suffice.➪ Script only function➪ Return only one default value➪ It mimics a Left Join and doesn't support other joins➪ It follows one-to-one or many-to-one mapping and not others.#QlikSense #Function #VT #F1

76

3 Comments

Hi All,Q: Where can anyone use the below set expression : "Sum( {< Product = Product + {P1} – {P2} >} Sales )"Understand it using a real case , here's a detailed example:∆∆ Scenario:You are a sales analyst at a retail company that tracks sales data for various products, identified by unique product IDs. You need to create a Qlik Sense dashboard that allows the sales team to dynamically analyze sales data. Specifically, you want to allow the team to see the total sales including a newly launched product while excluding a discontinued product.∆∆ Use Case:1. Newly Launched Product and Discontinued Product: • Newly Launched Product ID: P1 • Discontinued Product ID: P22. Current Selection: • The sales team has applied some selections on the dashboard such as country, area and selected some other products such as P3, P4 and P5 and is currently analyzing sales.3. Requirement: • The sales team wants to see the total sales for the currently selected products, but with the inclusion of the newly launched product (P = P1) and the exclusion of the discontinued product (P = P2).∆∆ Implementation:In the Qlik Sense dashboard to achieve this , you would use the expression "Sum( {<Product = Product + {P1} – {P2} >} Sales )" • Current Selection: Assume the current selection includes products with IDs P3, P4, and P5 and some other filters. • Product = Product : Keeps the current selection product(IDs P3, P4, P5). • + {P1} : Adds the newly launched product ID (P1). • – {P2} : Excludes the discontinued product ID (P2).∆∆ Result:The expression calculates the total sales for the following Products : • Included : P1 (newly launched), P3, P4, P5 (currently selected) • Excluded : P2 (discontinued)This allows the sales team to dynamically see the impact of including the new product and excluding the discontinued product on the total sales, without altering their initial selection criteria.#QlikSense #SetExpression #VT #SE2

66

Hi All,In Qlik Sense, we have set expressions to calculate and aggregate the measures and dimensions on the Frontend side.Sometimes developers face challenges to understand and interpret the set expressions efficiently.Starting now, I will share a series of use cases for set expressions to help others better understand them. I also welcome feedback from others to enhance my own knowledge.Q: What does this mean in Qlik Sense : Sum({<ID-={1}>} Sales) Here’s a breakdown of the components of this expression:1. Sum(): This function calculates the total sum of the specified field, in this case, "Sales".2. {<...>} : This is the set analysis syntax in Qlik Sense. It allows you to define a subset of the data to perform the aggregation on.3. <ID-={1}> : This is the set modifier part of the set analysis. It specifies how to filter the data. • "ID" refers to the field in your dataset that you want to filter. • "-" is the exclusion operator in set analysis, meaning you want to exclude certain values. • "={1}" specifies that you want to exclude the value "1" from the "ID" field.Putting it all together, "Sum({<ID-={1}>} Sales)" means: "Sum({<ID =ID - {1}>} Sales)" which means : - Calculate the sum of the "Sales" field.- Exclude all records where the "ID" field has the value "1".Thanks#QlikSense #SetExpression #VT #SE1

102

4 Comments

#Vote #VoteIndia #ElectionDayTomorrow #Phase226 Apr 2024....Lok Sabha seats going to polls in phase 2 of 2024 General Elections —1) Bihar - Kishanganj, Katihar, Purnia, Bhagalpur, Banka2) Assam - Karimganj, Silchar, Mangaldoi, Nawgong, Kaliabor3) Chhattisgarh - Rajnandgaon, Mahasamund, Kanker4) Jammu and Kashmir - Jammu5) Karnataka - Udupi Chikmagalur, Hassan, Dakshina Kannada, Chitradurga, Tumkur, Mandya, Mysore, Chamarajanagar, Bangalore Rural, Bangalore North, Bangalore Central, Bangalore South, Chikballapur, Kolar6) Kerala - Kasaragod, Kannur, Vatakara, Wayanad, Kozhikode, Malappuram, Ponnani, Palakkad, Alathur, Thrissur, Chalakudy, Ernakulam, Idukki, Kottayam, Alappuzha, Mavelikkara, Pathanamthitta, Kollam, Attingal, Thiruvananthapuram7) Madhya Pradesh - Tikamgarh, Damoh, Khajuraho, Satna, Rewa, Hoshangabad, Betul8) Maharashtra - Buldhana, Akola, Amravati (SC), Wardha, Yavatmal-Washim, Hingoli, Nanded, Parbhani9) Manipur - Outer Manipur10) Rajasthan - Tonk-Sawai Madhopur, Ajmer, Pali, Jodhpur, Barmer, Jalore, Udaipur, Banswara, Chittorgarh, Rajsamand, Bhilwara, Kota, Jhalawar-Baran11) Tripura - Tripura East12) Uttar Pradesh - Amroha, Meerut, Baghpat, Ghaziabad, Gautam Budh Nagar, Aligarh, Mathura, Bulandshahr13) West Bengal - Darjeeling, Raiganj, Balurghat#Bihar #Assam #Chhattisgarh #Jammu #Karnataka #Banglore #Kerala #MadhyaPradesh #MP #Maharashtra #Mumbai #Manipur #Rajasthan #Tripura #UttarPradesh #UP #WestBengal #Kolkata

6

आप सभी को दिवाली की ढेर सारी हार्दिक शुभकामनाएं l 🪔🎇🙏#diwali #celebration

12

ChatGPT has something to say for ZS . ***A poem on ZS*** ZS, a symbol of growth and fame,A company with power, its name.A leader in tech, a true game-changer,Innovating and disrupting, always a great performer.Its employees are sharp and bright,Working day and night, with all their might.A culture of excellence, they strive to uphold,Making a positive impact, both bold and bold.From data and cloud, to marketing and sales,ZS helps businesses reach new scales.With clients across the globe, they reign supreme,Providing solutions that are both practical and mean.So here's to ZS, a company so fine,May its success continue, with each passing line.A shining example of what can be achieved,When dedication and hard work are believed. #success #growth #tech #culture #power #data #leader #work

18

Happy Diwali to you and your family!On this day, Prabhu Shree Ram 🚩🙏🏻 return to ayodhya after 14 years long exiled, we wishing that this Diwali brings prosperity and more opportunities for all of us!May the lights of Diwali Diyas fill your home with wealth, health, happiness, and everything that Brings You Joy!✨✨Regards Vikas Tiwari

13