Kategorien
Allgemeines Qlik Script

Datamodelling Qlik Concatenate, Power BI Combine, SQL Union

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 understand faster how both world solve the same issues;)

The issue is simple and I use it very often for the appending two fact tables with each other. I keep this solutions “small” so I’m not showing you how to create a “manual” demo table…

Solution in SQL

Select
 eventname
 ,timestamp
FROM table1
UNION ALL
Select
 eventname
 ,timestamp
FROM table2

So this looks quiet simple 😉

Solution in Qlikscript

Table1:
LOAD
 eventname
 ,timestamp
FROM table1.csv;

Concatenate(Table1)
LOAD
 eventname
 ,timestamp
FROM table2.csv;

Looks simple too!;)

Solution in PowerQuery

...
 table1 = Source,
 table2 = SourceTwo,
 ResultTable = Table.Combine({table1,table2}),
...

This seems to be the shortest code sample, anyway you should not forgett, that the datasources are different, so for example 1, this can only be run on a SQL-Server. Qlik Script includes the datasource already in the script (csv-files) and for powerquery I ignored to add the connection to the datasources, which would have blown up the sample!

Kategorien
Allgemeines Allgemeines

Qlik Sense SetAnalysis and Power BI Dax Comparisson

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 Power BI. Maybe some of the examples need more explanation? Feel free to ask, otherwise it’s as short as possible, so I can keep an overview.

Total Sales for all years

QS: SUM({$<Calendar.Year=>} TotalSales)
PowerBI: CALCULATE(SUM(TotalSales);FILTER(ALL(Calendar)))

Kategorien
Allgemeines

Excel macro loop by sheet

Collect data from multiple excel tabs and put them together at a new collector sheet:

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
Kategorien
Allgemeines

MS SQL PIVOT with text as Fieldtype

WITH PivotData AS
(
SELECT
       ItemCodeBase.ItemCode AS ItemCode
	  ,CAST(ITEMTEXT.[FieldAlias] as VARCHAR(MAX)) AS FieldAlias
	  ,CAST(MUTLITEXT.Trans as VARCHAR(MAX)) AS Trans     
  FROM DWHLN_TEST.dbo.ItemsTextCheckCreh as ItemCodeBase
	  inner join SAP002.SAP_Master.dbo.OMLT AS ITEMTEXT on ITEMTEXT.PK = ItemCodeBase.ItemCode  COLLATE DATABASE_DEFAULT
	  INNER JOIN SAP002.SAP_Master.dbo.MLT1 AS MUTLITEXT ON ITEMTEXT.TranEntry = MUTLITEXT.TranEntry
  where 
	1=1
    AND MUTLITEXT.LangCode = '9' and FieldAlias in ('U_COR_BU_TXTS','U_COR_BU_TXTQ')
)

SELECT 
	ItemCode
	,[U_COR_BU_TXTS]
	,[U_COR_BU_TXTQ]
FROM
	PivotData
	PIVOT(
		MAX (Trans)
		FOR FieldAlias in ([U_COR_BU_TXTS],[U_COR_BU_TXTQ])) AS P;

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.

Kategorien
Allgemeines

Power BI and MS CRM Entity Tables

Temporary Entity Tables build with DAX Query and using the CRM Tables:

  • StringMapBase
  • MetadataSchema.Entity
Entity and Translation
LeadStatus = CALCULATETABLE(StringMapBase;FILTER('MetadataSchema Entity';'MetadataSchema Entity'[Name] = "Lead");FILTER(StringMapBase;StringMapBase[LangId]=1033 && StringMapBase[AttributeName] = "StateCode"))
Kategorien
Allgemeines

Power BI Calender DAX

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 statement below:

Date = 
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"%DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"BusinessDayOfWeek"; WEEKDAY ( [Date] ) & " " & FORMAT([Date];"ddd");
"DayInMonth"; DAY([Date]);
"BusinessDayInMonth"; DAY([Date]);
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" );
"CYTD"; if([Date] <= TODAY() && [Date] >= DATE(YEAR(TODAY());1;1);1;0);
"PYTD"; if([Date] <= TODAY()-365 && [Date] >= DATE(YEAR(TODAY()-365);1;1);1;0);
"CYLastWeek"; IF(YEAR([Date]) = YEAR(Today()) && WEEKNUM([Date]) = WEEKNUM(TODAY()) - 1;1;0);
"CYThisMonth"; IF(YEAR([Date]) = YEAR(Today()) && MONTH([Date]) = MONTH(TODAY());1;0);
"CYLastMonth"; IF(YEAR([Date]) = YEAR(Today()) && MONTH([Date]) = MONTH(TODAY()) - 1;1;0)
)

The funny part is, that PowerBI provides an “autocalender” function, which goes through the whole dataset and searches for the lowest date value and the latest date value, which can produce a hugh amount of data if you have e.g. 31.12.9999 as a max future date…

Kategorien
Allgemeines

T-SQL read tables and columns from a linked server

SELECT lst.name AS Table_Name, 
       lsc.name AS Column_Name, 
       lsc.max_length, 
       (schema_id) As Schema_name

FROM [SERVER].[DB].[sys].[tables] lst
    INNER JOIN [SERVER].[DB].[sys].[columns] lsc
        ON lst.OBJECT_ID=lsc.object_id

ORDER BY schema_name, lst.name, lsc.name
Kategorien
Allgemeines

T-SQL execute procedure for each row of a master table

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:

DECLARE @oneid int -- or the appropriate type

DECLARE the_cursor CURSOR FAST_FORWARD
FOR SELECT spro.Id  
    FROM SomeTable as spro 
        INNER JOIN [Address] addr ON addr.Id = spro.Id  
        INNER JOIN City cty ON cty.CityId = addr.CityId 
    WHERE cty.CityId = @CityId

OPEN the_cursor
FETCH NEXT FROM the_cursor INTO @oneid

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC UpdateComputedFullText @oneid

    FETCH NEXT FROM the_cursor INTO @oneid
END

CLOSE the_cursor
DEALLOCATE the_cursor
Kategorien
Allgemeines Jedox

Jedox – User CRUD – Manage Dimensions within worksheet

Usecase

Sometimes it´s needed that a user can add elements to a dimension without accessing the modeller – e.g. you wanna budget personal ressources and want to allow power users to add new placeholders to the employee dimension.

Entry mask for new employees

To get this working, you can use multiple ways on how to pass values from the worksheet, to the script – I prefered to use “names” for the parameters I wanna pass to my php:

Name Manager

Then with the “add” button, I use the following macro to insert the new element to the selected parent (trop down) and add the attributes.

/**
* Author <philipp@frenzel.net>
* Create a new ressource with name - Full_Name, Start_Date
*/

function _btnAdd_Click ()
{
  //First Read the values given by formula
  $connection = activeworkbook()->names->item("RNG_CONNECTION")->value;
  $inpFull_Name = activeworkbook()->names->item("crud_name")->value;
  $inpStart_Date = activeworkbook()->names->item("crud_start_date")->value;
  
  //Second retrieve variables
  $inpParentRessource = retrieve_variable("RESOURCES");
  
  //Third add a new Element to the dimension
  $key = $inpFull_Name . "(" . $inpParentRessource . ")";
  application()->palo_eadd($connection,"Resources","N",$key,$inpParentRessource,1,null);
  
  //Forth add the attrbutes to the new element
  application()->palo_setdata($inpFull_Name,false,$connection,"#_Resources","Full_Name",$key);
  application()->palo_setdata($inpStart_Date,false,$connection,"#_Resources","Start_Date",$key);
  
  return __recalc();
}

After you added the new element, with the recalc call at the end of the script, the dyna range should refresh immediatly and you should see the new element.

Kategorien
Allgemeines QlikView Workarounds Tips n Tricks

Qlik XML aus Feld in Tabelle einlesen

Ausgangslage

Der Kunde liefert eine Wertetabelle, bei der in einer der Spalten XML-Daten ohne feste Struktur vorhanden sind. Diese sollen so aufbereitet werden, dass diese Struktur als eigene Felder im Datenmodel aufscheinen.

Tabelle:

idAttributeAttributevalueFromDate
11<Attr1>123</Attr1><name>Test</name>42005
22<price>10.50</price>41911

Vorgehen

  • Daten ergänzen <root> node
  • Tabelle in XML Textdatei exportieren .xml (txt)
  • Tabelle als XML wieder einlesen (Assistent)