Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Joining Multiple Cross Tables Including Images as a data point

I have a table which looks like the following...

Audit_IDInspectorObservation A ScoreObservation A CommentObservation A PhotoObservation B ScoreObservation B CommentObservation B PhotoObservation C ScoreObservation C CommentObservation C Photo
1001John DoePassGood Job(Image)FailLack 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_IDInspectorObservationScoreCommentPhoto
1001John DoeAPassGood Job(Image)
1001John DoeBFailLack of Training(Null)
1001John DoeCPass(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!

1 Solution

Accepted Solutions
MVP
MVP

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

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;

6 Replies

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

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;


Capture.PNG

MVP
MVP

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

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;

Not applicable

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

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

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

Not applicable

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

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

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

Community Browser