Split the same table load to different collums

[mestredigital]

Dear colleagues,

How can I achieve the following table desing starting from the table load below?

Table1:
Load 
	DATE as Sale_Date,
    Sum(TOTAL_SALE) as Sales_Amount
FROM [lib://QVD_FOLDER (hlbr_qlik-ti)/Sales.qvd](qvd)
WHERE ([DATE] >= '2019-01-01' and [DATE] <= '2020-12-31')
Group By DATE;


Sale_Date_2019	Sales_Amount_2019	Sale_Date_2020	Sales_Amount_2020	Daily_Difference
01/06/2019		$139.076			01/06/2020		$345.691		 	$206.615
03/06/2019		$459.111         	02/06/2020    	$535.004         	$ 75.893
04/06/2019		$721.912         	03/06/2020    	$708.563         	-$ 13.349
05/06/2019		$691.872         	04/06/2020    	$667.678         	-$ 24.194
06/06/2019		$733.013         	05/06/2020    	$632.825         	-$ 100.188
07/06/2019		$651.805         	06/06/2020    	$125.194         	-$ 526.611
08/06/2019		$44.254          	07/06/2020    	$116.795         	$ 72.541
10/06/2019		$590.524         	08/06/2020    	$604.515         	$ 13.991
11/06/2019		$859.291         	09/06/2020    	$810.381         	-$ 48.910
12/06/2019		$731.483         	10/06/2020    	$814.206         	$ 82.723
13/06/2019		$705.645         	11/06/2020    	$342.245         	-$ 363.400
14/06/2019		$697.160         	12/06/2020    	$795.376         	$ 98.217
15/06/2019		$75.646          	13/06/2020    	$141.485         	$ 65.840
17/06/2019		$822.552         	15/06/2020    	$256.999         	-$ 565.552

[ArnadoSandoval]

Hi @mestredigital 

There are several options to go about resolving your issue, I will propose two, the first one resolve your problem on the script, the second implements a solution on the front-end user interface.

I created a mocked QVD files with two years of sales transactions (date-sales) and a calendar as part of the solution attached (50.Split Transactions.qvf).

Script Solution:

• We concatenated year 2019 transactions, renaming their date (Today) and sales fields to Sales_Date_2019 and Sales_Amount_2019 with year 2020 transactions, renaming them as Sales_Date_2020 and Sales_Amount_2020.
• The concatenation logic introduced the Date_Index field, its expression for both sets of transactions, is: 2019 + Month("Today") + Day("Today"), this field has a common value for transactions recorded on each year.
• After concatenating the sales transactions, splitting them in columns by year, a summary is calculated based on the Date_Index field, returning the Min() or Max() on the date columns (It does not matter if you use Min or Max), and the Sum of the Sales figures per year; this summary flatten the data, so transactions on the same date in both years will be on the same row.
• The Calendar table also feature the Date_Index field calculated with the same expression.

User Interface (Front-End) Solution:

• The script does not split-concatenate-summarize the data. All this transformation is done with a Table.
• The first column on this table was named "Index", it has the expression: 2019 + Month("Tran_Date") + Day("Tran_Date")
• Note: The table with all the sales transactions features the columns Tran_Date and Tran_Sales to avoid synthetic keys.
• The expression for the column: Sales_Date_2019 is:   Max(If(Year(Tran_Date)= 2019, Tran_Date))
• The expression for the column: Sales_Amount_2019 is: Sum(If(Year(Tran_Date) = 2019, Tran_Sales, 0))
• The expression for the columns Sales_Date_2020 and Sales_Amount_2020 applied filters for the appropriated year.

Considerations:

• My sales transactions are continuous in each year without gaps, it should not be a problem if the data contains gaps (a gap is missing days).
• You may need to adjust the expression used to calculated the Index column, I based its starting value on the year 2019.
• The logic is valid to show sales transactions side by side.
• The years 2019 and 2020 are hard-coded in the columns names, for a generic solution you should figure out a way to make them dynamic.

Attachments:

• The qvf application with its two QVDs.

Hope this helps,

[ArnadoSandoval]

@mestredigital 

I was unable to attache the QVDs, the application has the logic to build them!

One last try to load them!