The last 6 months have been quiet full of many new things to learn – after working more than 10 years with qlik and SQL a new player named “Power BI” has been added to my skills. So getting more and more used to it, I feel, like I should give other qlik developers to […]
Read More →
Download the latest Visual Studio Community Edition (about 4gb). Then start a new Project “Console VB.NET” and use the following code for executing a stored procedure on your MS SQL Server:
Read More →
For my new job I have to move my skills from qlik to microsoft power bi which – at the beginning – is quite challenging. So to keep a bridge in my mind and to speed up my knowledge curve, I document here the samples on how thinks work in qlik sense/view and how in […]
Read More →
Collect data from multiple excel tabs and put them together at a new collector sheet:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
Sub WorksheetLoop() Dim WS_Count As Integer Dim I As Integer ' Set WS_Count equal to the number of worksheets in the active ' workbook. WS_Count = ActiveWorkbook.Worksheets.Count ' Begin the loop. For I = 1 To WS_Count ' Insert your code here. ' The following line shows how to reference a sheet within ' the loop by displaying the worksheet name in a dialog box. MA_NAME = ActiveWorkbook.Worksheets(I).Name vJanuar = ActiveWorkbook.Worksheets(I).Cells(8, 3) vFebruar = ActiveWorkbook.Worksheets(I).Cells(8, 4) vMarch = ActiveWorkbook.Worksheets(I).Cells(8, 5) vzJanuar = ActiveWorkbook.Worksheets(I).Cells(9, 3) vzFebruar = ActiveWorkbook.Worksheets(I).Cells(9, 4) vzMarch = ActiveWorkbook.Worksheets(I).Cells(9, 5) ActiveWorkbook.Worksheets(239).Cells(I, 1) = MA_NAME ActiveWorkbook.Worksheets(239).Cells(I, 3) = vJanuar ActiveWorkbook.Worksheets(239).Cells(I, 4) = vFebruar ActiveWorkbook.Worksheets(239).Cells(I, 5) = vMarch ActiveWorkbook.Worksheets(239).Cells(I, 7) = vzJanuar ActiveWorkbook.Worksheets(239).Cells(I, 8) = vzFebruar ActiveWorkbook.Worksheets(239).Cells(I, 9) = vzMarch Next I End Sub |
The trick is, that you cast the text/string into varchar and then you can use MAX as aggregation type to return the readable value.
Read More →
Temporary Entity Tables build with DAX Query and using the CRM Tables: StringMapBase MetadataSchema.Entity
Read More →
Generating a calender was a very important task within qlik sense/view – so even as the date functions within power query or dax are awesome, I still prefer to have an “inbuild” calender. To achieve this goal, you can switch to the “Modeling” view -> add a “Table” and then just copy and paste the […]
Read More →
From time to time it’s needed to loop over the rows of a master table – e.g. different companies within your organisation are stored within different databases. So to avoid creating “long” SQL-scripts where you just change the db information, you can ease it up by:
Read More →
Usecase Imagine you have a customer who wants to split revenue by multiple dimensions not based upon total figures nor on percentual splits. E.g. you plan 10k and split it to 3 Segments by 20% Seg A 40% Seg B 40% Seg C And after this by ProductGroup and Product which are in one Dimension […]
Read More →