topic Re: Joining Multiple Cross Tables Including Images as a data point in QlikView

Joining Multiple Cross Tables Including Images as a data point

Tue, 17 May 2016 21:37:21 GMT

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!

Re: Joining Multiple Cross Tables Including Images as a data point

sunny_talwar — Tue, 17 May 2016 22:17:25 GMT

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;

Re: Joining Multiple Cross Tables Including Images as a data point

swuehl — Tue, 17 May 2016 22:27:19 GMT

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;

Re: Joining Multiple Cross Tables Including Images as a data point

Wed, 18 May 2016 16:03:22 GMT

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.

Re: Joining Multiple Cross Tables Including Images as a data point

sunny_talwar — Wed, 18 May 2016 16:27:18 GMT

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

Re: Joining Multiple Cross Tables Including Images as a data point

Wed, 18 May 2016 17:58:10 GMT

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

Re: Joining Multiple Cross Tables Including Images as a data point

sunny_talwar — Wed, 18 May 2016 18:08:40 GMT

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