Show dates as columns in a Straight table

[HussainIqbal]

Hello,

I've following Sample data (long/vertical):

Description, Date, Number
ABC, 2024-09-07, 8
DEF, 2024-09-08, 12
XYZ, 2024-09-09, 34
DEF, 2024-09-07, 28
ABC, 2024-09-05, 9

Goal: A tabular view (not a Pivot) where rows = Description and columns = specific dates, e.g.:

Description | 5 Sep | 7 Sep | 8 Sep | 9 Sep | Grand Total
ABC | 9 | 8 | | | 17
DEF | | 28 | 12 | | 40
XYZ | | | | 34 | 34

What’s the best practice in Qlik Sense to achieve this without a Pivot when the timeline is large?

[Oleg_Troyansky]

The best practice to achieve this in Qlik Sense (especially when the timeline is long) is a Pivot table. Anything else would be like fitting a square peg in a round hole.

[Or]

You could Crosstable() the data source. You're going to have to pivot it one way or the other - either in the script or using a front-end pivot. 

[ollis]

Some days I don't like to use pivot tables either... Hope you find this piece of script useful!

table2:  load * inline [

Description, Date, Number

ABC, 2024-09-07, 8

DEF, 2024-09-08, 12

XYZ, 2024-09-09, 34

DEF, 2024-09-07, 28

ABC, 2024-09-05, 9

];

tablesum: load Description as Description2, sum(Number) as "Grand Total" Resident table2 group by Description;

table: generic load * Resident table2;

drop table table2;

for i=0 to NoOfTables()

    if TableName($(i)) <>'tablesum' then

tables: load tablename($(i)) as tablename AutoGenerate 1;

endif

next i

tab: load Concat(distinct tablename,',') as groupby Resident tables where tablename <> 'table';

let groupby2 = purgechar(peek('groupby',0),'tables.');

let groupby3 = left(groupby2,(len(groupby2)-1));

let groupby = '['& replace(groupby3,',','],[') & ']'; 

trace groupby = $(groupby);

let sumby = replace(replace(groupby,'[','sum(['),']','])');

trace sumby = $(sumby);

drop table tab;

for each table in FieldValueList('tablename')

    if isnull(TableNumber('mastertable')) = -1 then 

    mastertable: load 0 as 0 AutoGenerate 0;

endif

Concatenate mastertable: load * resident [$(table)];

    drop table [$(table)];

next table

drop field [0], tablename;

Master2: load Description, $(sumby) resident mastertable group by Description;

left join (Master2) load Description2 as Description, "Grand Total" resident tablesum; 

drop table mastertable,tablesum;