Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table which looks like the following...
Audit_ID | Inspector | Observation A Score | Observation A Comment | Observation A Photo | Observation B Score | Observation B Comment | Observation B Photo | Observation C Score | Observation C Comment | Observation C Photo |
---|---|---|---|---|---|---|---|---|---|---|
1001 | John Doe | Pass | Good Job | (Image) | Fail | Lack of Training | (Null) | Pass | (Null) | (Null) |
In this format every audit represents on record. I would like to create a field specific to the observation type, then create a field with the score, comment, then photo. Essentially, creating 3 records for this audit or as below...
Audit_ID | Inspector | Observation | Score | Comment | Photo |
---|---|---|---|---|---|
1001 | John Doe | A | Pass | Good Job | (Image) |
1001 | John Doe | B | Fail | Lack of Training | (Null) |
1001 | John Doe | C | Pass | (Null) | (Null) |
As a side note, this table has over 70 different observations, not just the 3 used in the example.
I would assume the correct path is to create 3 cross tables, one for the Score, Comment and the Photo. I need help with the syntax to join all three cross tables. Then recognize the image with the Bundle Info command. I have used Re: How to show the bit photo in the QlikView as a reference on "how to" for loading images, but still not sure if its working with the cross tables.
Thanks to all in advance for the help!
Blake, for your 70+ observations, maybe changing over time, you can do the split and JOIN also like this:
INPUT: // This table can then be loaded using LOAD * , if the qualifiying fields come first
CROSSTABLE (ObservationType, Value,2)
LOAD Audit_ID,
Inspector,
[Observation A Score],
[Observation A Comment],
[Observation A Photo],
[Observation B Score],
[Observation B Comment],
[Observation B Photo],
[Observation C Score],
[Observation C Comment],
[Observation C Photo]
FROM
[https://community.qlik.com/thread/217264]
(html, codepage is 1252, embedded labels, table is @1);
RESULT:
LOAD Audit_ID, Inspector,Trim(TextBetween(ObservationType, 'Observation','Score')) as Observation, Value as Score
RESIDENT INPUT
WHERE Wildmatch(ObservationType,'Observation*Score');
LEFT JOIN (RESULT)
LOAD Audit_ID, Inspector, Trim(TextBetween(ObservationType, 'Observation','Comment')) as Observation, Value as Comment
RESIDENT INPUT
WHERE Wildmatch(ObservationType,'Observation*Comment');
LEFT JOIN (RESULT)
LOAD Audit_ID, Inspector, Trim(TextBetween(ObservationType, 'Observation','Photo')) as Observation, Value as Photo
RESIDENT INPUT
WHERE Wildmatch(ObservationType,'Observation*Photo');
DROP TABLE INPUT;
Try this:
Table:
CrossTable (Observation, Value, 2)
LOAD Audit_ID,
Inspector,
[Observation A Score]& '|'&[Observation A Comment]& '|'&[Observation A Photo],
[Observation B Score]& '|'&[Observation B Comment]& '|'&[Observation B Photo],
[Observation C Score]& '|'&[Observation C Comment]& '|'&[Observation C Photo]
FROM
[https://community.qlik.com/thread/217264]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
LOAD Audit_ID,
Inspector,
TextBetween(SubField(Observation, '|', 1), ' ', ' ') as Observation,
SubField(Value, '|', 1) as Score,
SubField(Value, '|', 2) as Comment,
SubField(Value, '|', 3) as Photo
Resident Table;
DROP Table Table;
Blake, for your 70+ observations, maybe changing over time, you can do the split and JOIN also like this:
INPUT: // This table can then be loaded using LOAD * , if the qualifiying fields come first
CROSSTABLE (ObservationType, Value,2)
LOAD Audit_ID,
Inspector,
[Observation A Score],
[Observation A Comment],
[Observation A Photo],
[Observation B Score],
[Observation B Comment],
[Observation B Photo],
[Observation C Score],
[Observation C Comment],
[Observation C Photo]
FROM
[https://community.qlik.com/thread/217264]
(html, codepage is 1252, embedded labels, table is @1);
RESULT:
LOAD Audit_ID, Inspector,Trim(TextBetween(ObservationType, 'Observation','Score')) as Observation, Value as Score
RESIDENT INPUT
WHERE Wildmatch(ObservationType,'Observation*Score');
LEFT JOIN (RESULT)
LOAD Audit_ID, Inspector, Trim(TextBetween(ObservationType, 'Observation','Comment')) as Observation, Value as Comment
RESIDENT INPUT
WHERE Wildmatch(ObservationType,'Observation*Comment');
LEFT JOIN (RESULT)
LOAD Audit_ID, Inspector, Trim(TextBetween(ObservationType, 'Observation','Photo')) as Observation, Value as Photo
RESIDENT INPUT
WHERE Wildmatch(ObservationType,'Observation*Photo');
DROP TABLE INPUT;
Thank you, you are correct in assuming there would be changes/additions to the audits. Your solution works keeping in mind the large number of fields to work with.
I would be curious to hear what kind of issue you ran into using the solution I provided above? I thought it was a fairly straight forward approach, but would be interested in knowing its drawback.
Best,
Sunny
Your solution would work, both were very helpful. The only drawback is the prep needed to apply that method for over 70 different observations. My initial thoughts seemed to take me into excel and do a lot of prep before pushing the data into the script editor.
Blake
I guess I am still not sure how one is better then the other. But I guess I don't really understand if you have 70 different rows or 70 different columns.
Anyways, I am glad you got what you were looking for.
Best,
Sunny