topic Re: Split the same table load to different collums in App Development

Split the same table load to different collums

mestredigital — Sat, 16 Nov 2024 01:56:16 GMT

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

Re: Split the same table load to different collums

ArnadoSandoval — Sat, 18 Jul 2020 00:51:51 GMT

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,

Re: Split the same table load to different collums

ArnadoSandoval — Sat, 18 Jul 2020 00:53:42 GMT

@mestredigital

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

One last try to load them!