Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello to all
I have two columns from QVD table:
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?
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;
you welcome
Hi
May be this
TabQVD:
generic
Load
SerialL3,
'TaskLabel_'&autonumber(rowno(),TaskLabel),TaskLabel;
From QVD
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
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;
Output.
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
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;
Output:
thanks a lot 😉 this is what I need 🙂
you welcome