Data Import and Processing Issue.

[#1]

Hi Everyone!

I have a question about an issue that I just cannot resolve. I am importing data from an XML source. See attached file for sample of the data. The data imports into two tables into QlikView.  One table holds the general TroubleTicket information, and the other table holds the state history for the troubletickets.  In SQL this is a one to many relationship.

Now I need to do a lot of additional processing on this data, and my reports must filter on a bunch of fields as well.  One of my first issues is how do I select a record, and then only the lastested state information from the State table? Each state has a timestamp, so I need to select the greatest one for each TroubleTicket and that will serve as both the current and final status for that ticket as this point an time. I know how to do this in SQL, but I cannot figure it out in QlikView.

Thanks,

-Marek

[swuehl]

Marek,

do you want to filter on the final status in the load script, i.e. limiting your data model to the filtered data? Or do you want to keep all data and just filter in a chart object?

[#3]

Hmm...

I think I would filter on the load script.  Because once I have the values I need to create additional calculated fields for my reports. 

If it is not too much to ask, can we do both?  i.e. keep a raw_data table with all the data and filter in the chart object? and create another table with the processed data?

[swuehl · Accepted solution]

Sure, should be possible.

To start, if you want to filter the state table for each issue on last or final ID and date/time, you could do something like:

STATE:

LOAD ID,

    Creationdate,

    Creationtime,

    %Key_ISSUE_D1DDF45FA4C35DB9    // Key to parent table: SIRE/ISSUE

FROM C:\Users\Stefan\AppData\Local\Opera\Opera\temporary_downloads\sampledata.xml (XmlSimple, Table is [SIRE/ISSUE/STATE]);

ISSUE:

LOAD ID,

    Currentstate,

    Submitdate,

    Submittime,

    Lasttransdate,

    Lasttranstime,

    Application,

    Classification,

    Occurredinenvironment,

    Priority,

    Shortdescription,

    %Key_ISSUE_D1DDF45FA4C35DB9    // Key for this table: SIRE/ISSUE

FROM C:\Users\Stefan\AppData\Local\Opera\Opera\temporary_downloads\sampledata.xml (XmlSimple, Table is [SIRE/ISSUE]);

// End of [sampledata.xml] LOAD statements

left join LOAD

  firstsortedvalue(ID, -(Creationdate+Creationtime)) as FinalID

,firstsortedvalue(Creationdate, -(Creationdate+Creationtime)) as FinalDate

,firstsortedvalue(Creationtime, -(Creationdate+Creationtime)) as FinalTime

,%Key_ISSUE_D1DDF45FA4C35DB9    // Key to parent table: SIRE/ISSUE

resident STATE group by %Key_ISSUE_D1DDF45FA4C35DB9;

[#5]

Thanks a millions!  That did the trick!!!

Much appreciated!!!

[swuehl]

You're welcome.

In a similar way, you could also do this in the UI.

For example create a straight table chart with dimension %Key_ISSUE..... and then as expression for FinalID e.g.

=firstsortedvalue(ID, -(Creationdate+Creationtime))

Have a nice weekend,

Stefan