Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
There is one requirement in report need to show all problem details in one row against the serial number:
This is my source table:
Serial Number | Problem Detail |
---|---|
1234567898766 | Broken |
1234567898766 | lock not enabled |
1234567898766 | no power |
1234567898766 | screen broken |
67895423453489 | no charge |
67895423453489 | screen Broken |
98765431245678 | no charge |
768809215272920 | No cover |
The above one is my Table , Each serial number has maximum 4 problem details and they are 295 different problem details for 4 questions, I would like to get the data like below. There are millions of records. please suggest how to proceed with load script.
Finale Table Out put:
Serial Number | Problem detail 1 | Problem detail 2 | Problem detail 3 | Problem detail 4 |
---|---|---|---|---|
1234567898766 | Broken | lock not enabled | no power | screen broken |
67895423453489 | no charge | screen Broken | - | - |
98765431245678 | no charge | - | - | - |
768809215272920 | No cover | - | - | - |
Thanks for your help.
Giri
Dimension: [Serial Number]
Expression1: subfield(concat(distinct [Problem Detail],','),',',1)
Expression2: subfield(concat(distinct [Problem Detail],','),',',2)
Expression3: subfield(concat(distinct [Problem Detail],','),',',3)
Expression4: subfield(concat(distinct [Problem Detail],','),',',4)
see attached file
Here is one way to do this in script
Table:
LOAD *,
If([Serial Number] = Previous([Serial Number]), RangeSum(Peek('ProbNum'), 1), 1) as ProbNum;
LOAD * INLINE [
Serial Number, Problem Detail
1234567898766, Broken
1234567898766, lock not enabled
1234567898766, no power
1234567898766, screen broken
67895423453489, no charge
67895423453489, screen Broken
98765431245678, no charge
768809215272920, No cover
];
FinalTable:
LOAD Distinct [Serial Number]
Resident Table;
FOR i = 1 to FieldValueCount('ProbNum')
LET vField = FieldValue('ProbNum', $(i));
Left Join (FinalTable)
LOAD [Serial Number],
[Problem Detail] as [Problem Detail $(vField)]
Resident Table
Where ProbNum = $(vField);
NEXT i;
DROP Table Table;
Hi,
Plase reffer Crosstable with cross tab.
Cross tables are table where facts are divided on the basis of dimension values.
for example we have sales for every month. then, if in our database we load sales amounts as a separated field for every month as
Product Jan Feb Mar etc
a 100 200 300
So, basically we have 1 single fact as sales amount here, and jan-dec are dimension memebers of month dimension but we are creating different fields for every dimension member and treating them as different field.
Loading data this way, makes it difficult to properly aggregrate the data over different dimensions and difficult to do analysis.
So, we convert this crosstable into a normal table.
using
crosstable (Month, Amount)
load
*
from a.qvd(qvd);
then we will get the same table as
Product Month Amount
a jan 100
a feb 200
a mar 300
Hope this solve your problem
Thanks
Hi,
Why you dont try to use Crosstable?
TMP:
Crosstable (SERIAL_NUMBER, PROBLEM_DETAIL)
LOAD
*
FROM YourQVD
or generic load?
Problems:
Generic
LOAD [Serial Number],
'Problem Detail ' & AutoNumber(RecNo(),[Serial Number]),
[Problem Detail]
FROM
[https://community.qlik.com/thread/276195]
(html, codepage is 1252, embedded labels, table is @1);
Also see: Use cases for Generic Load | Qlikview Cookbook
-Rob
I wish I'd read your answer before re-inventing the wheel! i'll happily delete my post, there's enough to read on this thread as it is.
Regards
Andrew
Hi,
Is there a way to put the values for 'problem detail' in 1 column, separated by ";" ?
Kind regards,
Carlos
You can use Concat() function
LOAD [Serial Number],
Concat([Problem Detail], ';') as [Problem Detail List]
Resident ...
Group By [Serial Number];