Joining Multiple Cross Tables Including Images as a data point

[#1]

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!

[sunny_talwar]

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;

[swuehl · Accepted solution]

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;

[#4]

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.

[sunny_talwar]

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

[#6]

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

[sunny_talwar]

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