Need help for transpose and count

[noviceneil]

Hello ,

          I am working on a set if client data so can't provide the real data. below are the similar dataset  :

ITEM_ID	Color	Type	Make	DIM4	Status	DATE
KEY 1	RED	BIKE	HONDA	AAAA	OPEN	01.01.2024
KEY 2	RED	BIKE	HONDA	AAAA	CLOSE	01.01.2024
KEY 3	BLUE	BIKE	YAMAHA	AAAA	OPEN	01.01.2024
KEY 4	BLUE	BIKE	HONDA	BBBBB	OPEN	01.01.2024
KEY 5	BLUE	BIKE	HONDA	AAAA	CLOSE	01.01.2024
KEY 6	BLUE	BIKE	HONDA	BBBBB	OPEN	01.01.2024
KEY 7	BLUE	BIKE	YAMAHA	AAAA	OPEN	01.01.2024
KEY 8	BLUE	BIKE	YAMAHA	BBBBB	CLOSE	01.01.2024
KEY 11	BLUE	BIKE	HONDA	AAAA	WAITING	01.01.2024
KEY 12	GREEN	BIKE	HONDA	BBBBB	OPEN	01.01.2024
KEY 13	GREEN	BIKE	HONDA	AAAA	OPEN	01.01.2024
KEY 14	GREEN	BIKE	HONDA	BBBBB	OPEN	01.01.2024
KEY 15	GREEN	BIKE	HONDA	AAAA	CLOSE	01.01.2024
KEY 16	GREEN	BIKE	HONDA	BBBBB	OPEN	01.01.2024
KEY 17	YELLOW	BIKE	HONDA	AAAA	CLOSE	01.01.2024
KEY 18	YELLOW	BIKE	HONDA	BBBBB	OPEN	01.01.2024
KEY 19	YELLOW	BIKE	HONDA	AAAA	OPEN	01.01.2024
KEY 20	YELLOW	BIKE	HONDA	BBBBB	CLOSE	01.01.2024
KEY 21	YELLOW	BIKE	HONDA	AAAA	CLOSE	01.01.2024
KEY 22	YELLOW	BIKE	HONDA	BBBBB	WAITING	01.01.2024

The actual table has around 900 total rows or sometimes even more. I need to count the number of cases for each Color, Type , Make and DIM4.. for example, GREEN HONDA BIKE BBBB has 3 open cases. Expected output provided below.

DATE	Color	Type	Make	DIM4	OPEN	CLOSE	WAITING
01.01.2024	RED	BIKE	HONDA	AAAA	1	1
01.01.2024	BLUE	BIKE	YAMAHA	AAAA	2
01.01.2024	BLUE	BIKE	HONDA	BBBBB	2
01.01.2024	BLUE	BIKE	HONDA	AAAA		1	1
01.01.2024	BLUE	BIKE	YAMAHA	BBBBB		1
01.01.2024	GREEN	BIKE	HONDA	BBBBB	3
01.01.2024	GREEN	BIKE	HONDA	AAAA	1	1
01.01.2024	YELLOW	BIKE	HONDA	AAAA	1	2
01.01.2024	YELLOW	BIKE	HONDA	BBBBB	1	1	1

Could you please help.

[Clement15 · Accepted solution]

Hello,

This should meet your need

Data:
Load
*
Inline [
ITEM_ID,Color,Type,Make,DIM4,Status,DATE
KEY 1,RED,BIKE,HONDA,AAAA,OPEN,01.01.2024
KEY 2,RED,BIKE,HONDA,AAAA,CLOSE,01.01.2024
KEY 3,BLUE,BIKE,YAMAHA,AAAA,OPEN,01.01.2024
KEY 4,BLUE,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 5,BLUE,BIKE,HONDA,AAAA,CLOSE,01.01.2024
KEY 6,BLUE,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 7,BLUE,BIKE,YAMAHA,AAAA,OPEN,01.01.2024
KEY 8,BLUE,BIKE,YAMAHA,BBBBB,CLOSE,01.01.2024
KEY 11,BLUE,BIKE,HONDA,AAAA,WAITING,01.01.2024
KEY 12,GREEN,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 13,GREEN,BIKE,HONDA,AAAA,OPEN,01.01.2024
KEY 14,GREEN,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 15,GREEN,BIKE,HONDA,AAAA,CLOSE,01.01.2024
KEY 16,GREEN,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 17,YELLOW,BIKE,HONDA,AAAA,CLOSE,01.01.2024
KEY 18,YELLOW,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 19,YELLOW,BIKE,HONDA,AAAA,OPEN,01.01.2024
KEY 20,YELLOW,BIKE,HONDA,BBBBB,CLOSE,01.01.2024
KEY 21,YELLOW,BIKE,HONDA,AAAA,CLOSE,01.01.2024
KEY 22,YELLOW,BIKE,HONDA,BBBBB,WAITING,01.01.2024

]
;

// NoConcatenate

Final:
Load
DATE,
Color,
Type,
Make,
DIM4,
count(if(Status='OPEN',ITEM_ID)) as OPEN,
count(if(Status='CLOSE',ITEM_ID)) as CLOSE,
count(if(Status='WAITING',ITEM_ID)) as WAITING
Resident Data
group by
DATE,
Color,
Type,
Make,
DIM4;

Drop table Data;

[noviceneil]

Thank you for your reply, but as there are more than 900 rows random combination present in the input table for one period(weekly), an inline table creation would not be possible.

After doing the transpose and count, I have to store it with a timestamp in a qvd and later need to read those qvd files in a loop for multiple periods. ( this part I can do if the first part is done as in my request)

[noviceneil]

any other ways to achieve this please?

[Clement15 · Accepted solution]

And with this?

Data:
Load
*
Inline [
ITEM_ID,Color,Type,Make,DIM4,Status,DATE
KEY 1,RED,BIKE,HONDA,AAAA,OPEN,01.01.2024
KEY 2,RED,BIKE,HONDA,AAAA,CLOSE,01.01.2024
KEY 3,BLUE,BIKE,YAMAHA,AAAA,OPEN,01.01.2024
KEY 4,BLUE,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 5,BLUE,BIKE,HONDA,AAAA,CLOSE,01.01.2024
KEY 6,BLUE,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 7,BLUE,BIKE,YAMAHA,AAAA,OPEN,01.01.2024
KEY 8,BLUE,BIKE,YAMAHA,BBBBB,CLOSE,01.01.2024
KEY 11,BLUE,BIKE,HONDA,AAAA,WAITING,01.01.2024
KEY 12,GREEN,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 13,GREEN,BIKE,HONDA,AAAA,OPEN,01.01.2024
KEY 14,GREEN,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 15,GREEN,BIKE,HONDA,AAAA,CLOSE,01.01.2024
KEY 16,GREEN,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 17,YELLOW,BIKE,HONDA,AAAA,CLOSE,01.01.2024
KEY 18,YELLOW,BIKE,HONDA,BBBBB,OPEN,01.01.2024
KEY 19,YELLOW,BIKE,HONDA,AAAA,OPEN,01.01.2024
KEY 20,YELLOW,BIKE,HONDA,BBBBB,CLOSE,01.01.2024
KEY 21,YELLOW,BIKE,HONDA,AAAA,CLOSE,01.01.2024
KEY 22,YELLOW,BIKE,HONDA,BBBBB,WAITING,01.01.2024
]
;

Temp:
Generic Load
DATE,
Color,
Type,
Make,
DIM4,
'Status_' & Status as StatusType,
Count(ITEM_ID) as Count
Resident Data
Group by
DATE,
Color,
Type,
Make,
DIM4,
Status;

Final:
Load DISTINCT
DATE,
Color,
Type,
Make,
DIM4
Resident Data;

For i = 0 to NoOfTables()-1 
Let vTableName = TableName($(i));
If WildMatch( '$(vTableName)', '*Status_*') Then
Left Join (Final) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
i=i-1
EndIf
Next

Drop Tables Data;

[noviceneil]

Thank you a lot...this works.