topic Re: Load header data as line item with each transaction in QlikView

Load header data as line item with each transaction

mazacini — Mon, 28 Sep 2015 08:06:09 GMT

See attached.

I have data in the format as indicated in the 'Source' tab on the attached.

I need the data to load in the format indicated in the 'Required' tab.

The problem I have is how to deal with the Date/Trip Ref field. In my original data, the file contains many different Date/Trip Ref headers.

I just need to add the Date/Trip Ref field as a line item alongside the related line item data below it.

Amy suggestions?

Regards

Joe

Re: Load header data as line item with each transaction

Gysbert_Wassenaar — Mon, 28 Sep 2015 08:12:21 GMT

That kind of source is generated from some other source system. That source system is where you should retrieve your data from. Or ask the owners of that system to provide data in a format that can be used for automated processing. What you now have is meant for humans to read. Qlikview is not designed to process such reports.

Re: Load header data as line item with each transaction

mazacini — Mon, 28 Sep 2015 10:16:13 GMT

Many thanks for your response Gysbert.

Unfortunately,the data is not available in any other format.

I take your point about the nature of these reports.

But, with respect (and I am a financial, not a technical person), whatever the truth about what Qlikview was designed for, I have previously loaded data in this format - with solutions provided by the community.

This is one of the things I really like about Qlikview - it's flexibility in handling this type of data.

And I would be able to overcome the current problem if there were only ONE trip per report. My problems arises from multiple trip instances on the one page.

I believe there is some combination of Peek, Previous and with a "flag delete" field that will work.

Any suggestions?

Regards

Joe

Re: Load header data as line item with each transaction

Gysbert_Wassenaar — Mon, 28 Sep 2015 10:29:39 GMT

I am a financial, not a technical person

Are you saying that financial persons are not allowed to ask or not capable of asking the owner of a system for a different format?

I have previously loaded data in this format

Uhm... then why are you asking again if you already have a working solution.

And I would be able to overcome the current problem if there were only ONE trip per report. My problems arises from multiple trip instances on the one page.

Are there any other requirements that your example excel document doesn't mention?

Re: Load header data as line item with each transaction

marcus_sommer — Mon, 28 Sep 2015 10:29:48 GMT

In general it would be possible to read and transform such data-structures with qlikview because the defintion in which row is the header, how many rows with which condition should be included and checking previous records - per peek() - and creating new columns with them is quite flexible especially in a cascade of load-statements which might be in a loop.

But before you could try if you get what you want with the extended options from table-wizard. But unfortunately is the documentation here very small but in the following book is an explanation available:

QlikView 11 for Developers

Barry Harmsen und Miguel Garcia

ISBN-13: 978-1849686068

In one of the other Books and literature (from Stephen Redmond) is another example of using the wizard but I don't know excactly which one but especially the above mentioned is general recommended as reference book.

Here - Re: Loading spreadsheets with different formatting - you find an example of loading not well structured data into qlikview without the wizard to get an idea what I mean. But at first you should follow the suggestion from gwassenaar, then the table-wizard and as worst-case you could try to load and transform it without them (you will need a lot of efforts for this).

- Marcus

Re: Load header data as line item with each transaction

mazacini — Mon, 28 Sep 2015 10:45:25 GMT

Hi Gysbert

Long story (and I did ask) - but the data is just not available in any other format.

What I meant to say was that I had previously loaded data that was not in an easy format for data load. The data was, as you describe, designed for reading, not loading.

But it was not in the exact format.

The data actually comes in csv. Otherwise, I think the excel file correctly describes the requirement.

Regards

Joe

Re: Load header data as line item with each transaction

mazacini — Mon, 28 Sep 2015 19:55:21 GMT

Ok. I think I have it sorted.

I loaded all the transaction fields AND the Date/Trip Ref fields only using

where (len(@7)>0 and not @7 like 'Run Ref') or wildmatch (@1,'*Ref:*')

So I loaded field 1 as Start - this field contains the actual Start data I need AND the Date/TripRef data also. (Note this latter data will uniquely contain a 'Date' string)

I then need to extract the Date/TripRef values from this field and create a new field.

I do a noconcatenate load into a temporary table, creating a flagdelete field FOR THOSE RECORDS WITH Date/TripRef DATA. I use the presence of the string 'Date' to generate the flag

if(left(Start,4)='Date',1) as FlagDelete

I also create a new field for the Date/TripRef data, again using the presence of the 'Date' string.

if(left(Previous(Start),4)='Date',Previous(Start) ,Peek(Date_TripRef)) as Date_TripRef

I drop my original table, then do another noconcatenate load, this time deleting the flagdelete field.

So to finish:

Table:

NoConcatenate

LOAD

Resident

TempTable2

Where FlagDelete <> 1 ;

DROP Field FlagDelete;

drop table TempTable2;