Table Columns to report sequentially (based on date) product contacts with a location

[CB-CAN]

Products are sent to a location on different dates with each data a separate row in the data.  I would like to have a table with first column the location, and then each column lists the product sent to that location with the first shipment being 2nd column and 2nd product shipment being 2nd column and so on.

This is the data loaded into QLIK Sense:

 

I would like he data to look like this: 

Thank you

[Daniel_Pilla]

Hi @CB-CAN ,

I am assuming that you want this table created in the load script. The script below will create a table in that format with a dynamic number of columns created for the 1st, 2nd, 3rd, 4th and so on dates for each location. Note that in your example above, all of the dates are unique per location. If you have the same date per location then you'd have to add some additional handling (right now it would just be added as another column).

There might be a more optimal way to achieve this, this was just the first method I came up with.

Model:

Script:

OriginalData:
LOAD 
	Location,
    Product,
    Date#(Date,'D/M/YYYY') AS Date
INLINE [
	Location	,Product	,Date
    QB1			,ABC		,1/1/22
    CB3			,ABC		,2/1/22
    DE5			,123		,10/1/22
    CB3			,Zzz		,5/2/22
    CB3			,YYY		,20/2/22
    QB1			,Zzz		,3/3/22
    DE5			,ABC		,3/3/22
    QB1			,YYY		,1/5/22
];

LET i = 1;
FOR EACH Location in FieldValueList('Location')
	Location$(i):
    NOCONCATENATE LOAD
    	'$(Location)' AS Location
    AUTOGENERATE 1;

	Dates:
	LOAD DISTINCT
    	Date AS DateLookup,
        Product
    RESIDENT OriginalData
    WHERE Location = '$(Location)'
    ORDER BY Date ASC;
    
    FOR z=1 to NoOfRows('Dates')
    	
        LEFT JOIN(Location$(i))
        LOAD DISTINCT
            '$(Location)' AS Location,
            Product AS [$(z)]
        RESIDENT Dates
        WHERE RecNo()=$(z);
        
    NEXT z
    
    LET i = $(i) + 1;
    DROP TABLES Dates;
            
NEXT Location

Data:
LOAD * INLINE [Temp];

FOR a = NoOfTables()-1 to 0 STEP -1;
	LET vTable=TableName($(a));
    IF WildMatch('$(vTable)','Location*') THEN
    	CONCATENATE ([Data]) LOAD * RESIDENT [$(vTable)];
		DROP TABLE [$(vTable)];
    ENDIF
NEXT a

DROP FIELD Temp;

Map_NumFormat:
MAPPING LOAD * INLINE [
	Int	,Suffix
    1	,st
    2	,nd
    3	,rd
];

Map_RenameFields:
MAPPING LOAD
	RecNo() AS Old,
    RecNo() & ApplyMap('Map_NumFormat',Mod(RecNo(),10),'th') AS New
AUTOGENERATE 100;

RENAME Fields USING Map_RenameFields;

Cheers,

[Ahidhar]

you can also do it using Generic Load

TAB:

LOAD 

Location,

    Product,

    Date#(Date,'D/M/YYYY') AS Date

INLINE [

Location ,Product ,Date

    QB1 ,ABC ,1/1/22

    CB3 ,ABC ,2/1/22

    DE5 ,123 ,10/1/22

    CB3 ,Zzz ,5/2/22

    CB3 ,YYY ,20/2/22

    QB1 ,Zzz ,3/3/22

    DE5 ,ABC ,3/3/22

    QB1 ,YYY ,1/5/22

];

NoConcatenate

TAB1:

load *,

if(Location='QB1',1,

if(Location='CB3',2,3)) as Flag

resident TAB  ;

drop Table TAB;

Tab:

Generic load Location as l1,num(Date) as Date,Product as p

resident TAB1 where Flag=1 order by Date;

t1:

load l1, [44562] as '1st' resident Tab.44562;

left join(t1)

load l1, [44623] as '2nd' resident Tab.44623;

left join(t1)

load l1, [44682] as '3rd' resident Tab.44682;

drop table Tab.44623;

drop table Tab.44562;

drop table Tab.44682;

Tab:

Generic load Location as l1,num(Date) as Date,Product as p

resident TAB1 where Flag=2 order by Date;

t2:

load l1, [44563] as '1st' resident Tab.44563;

left join(t2)

load l1, [44597] as '2nd' resident Tab.44597;

left join(t2)

load l1, [44612] as '3rd' resident Tab.44612;

drop table Tab.44563;

drop table Tab.44597;

drop table Tab.44612;

Tab:

Generic load Location as l1,num(Date) as Date,Product as p

resident TAB1 where Flag=3 order by Date;

t3:

load l1, [44571] as '1st' resident Tab.44571;

left join(t3)

load l1, [44623] as '2nd' resident Tab.44623;

drop table Tab.44571;

drop table Tab.44623;

TAB:

load l1 as Location,[1st],[2nd],[3rd] resident t1;

Concatenate

load l1 as Location,[1st],[2nd],[3rd] resident t2;

Concatenate

load l1 as Location,[1st],[2nd] resident t3;

drop table t1;drop table t2;drop table t3;