Pivot Table load statement

[lukeert19]

Hello to all

I have two columns from QVD table:

1. SerialL3
2. TaskLabel

I need to count how many time one Item, which in this case is SerialL3 pass throght one specific task which in this case is [TaskLabel].

Unfortunately I can't use "in line" function becase I don't know how many SerialL3 I can have and also for the taskLabel column 😞

below an example  which table I need to obtain 

SerialL3         TaskA    TaskB    TaskC     TaskD

1 2345                 2               1               0             0

2546                     1                0             0             0

35464                   0                0             1              2

hoe can help me to vole this my issue?

[brunobertels]

Hi 

May be this 

TabQVD:

generic

Load 

SerialL3,

'TaskLabel_'&autonumber(rowno(),TaskLabel),TaskLabel;

From QVD

[lukeert19]

Hello Brunobertels 

mmm If I use the function 'TaskLabel_'&autonumber(rowno(),TaskLabel),TaskLabel; I will get n columns where n is equals to number of rows right?

in this case I need to get only the distict values of this columns and count how many time one SerialL3 pass throght each  taskLabel

Thank again Brunobertels  for your time

[Saravanan_Desingh]

Are you looking something like this?

tab1:
LOAD * Inline [
SerialL3, TaskA, TaskB, TaskC, TaskD,
1 2345, 2, 1, 0, 0
2546, 1, 0, 0, 0
35464, 0, 0, 1, 2
];

tabX:
CrossTable(TaskLabel, Value)
LOAD * Resident tab1;

Left Join(tabX)
LOAD SerialL3, Count(TaskLabel) As Task_Count
Resident tabX
Where Value > 0
Group By SerialL3
;

Drop Table tab1;

[Saravanan_Desingh · Accepted solution]

Output.

[lukeert19]

Maybe I haven't explained well my problem.

I have to start from this table (more or less 180k records )

serial3   taskLabel

12345  taskXxxxx

12345  taskyyyyy

12345  taskyyyyy

12345  taskxxxYyy

54321  taskXxxxx

54321  taskxxxYyy

54321  taskWWWW

54321  taskWWWW

I have to convert the taskLabel to different columns end count how many times one serial number pass through one taskLabel.

The final table will be:

SerialL3  taskXxxxx taskyyyyy taskxxxYyy taskWWWW

12345          1                     2                     1                 0

54321         1                     0                  1                      2

I have to create this table without know which TaskLabel could I have and also for SerialL3

In first table as is possible to se the taskLabel could appear more one then time, for this  reason in the final table I need to have the district values

[Saravanan_Desingh · Accepted solution]

Check this.

tab1:
LOAD * Inline [
serial3, taskLabel
12345, taskXxxxx
12345, taskyyyyy
12345, taskyyyyy
12345, taskxxxYyy
54321, taskXxxxx
54321, taskxxxYyy
54321, taskWWWW
54321, taskWWWW
];

Gen:
Generic
LOAD serial3, taskLabel, Count(taskLabel) As Cnt
Resident tab1
Group By serial3, taskLabel
;

Drop Table tab1;

[Saravanan_Desingh]

Output:

[lukeert19]

thanks a lot 😉 this is what I need 🙂

[Saravanan_Desingh · Accepted solution]

you welcome