topic Data Type and Data Value in QlikView

Data Type and Data Value

Anonymous — Fri, 06 May 2011 14:03:01 GMT

I need to create the following as a single table. Is this possible in QlikView?

Data Type, Comma Separate Data Value

Header, 05/30/2010

Customer, Frank, 10 Riverview Drive, Springfield, MO

Customer, Sue, 21 Middlbury Rd, Parsippany, MD

Customer, Tom, 10 ABC drivethru, Southbury, CT

Product, Shoe, 1000

Product, Bag, 200

Footer, 5

Input data is below:

[Customer]:

LOAD * INLINE [

ID, Name, Address, City, State

1, Frank, 10 Riverview Drive, Springfield, MO

2, Sue, 21 Middlbury Rd, Parsippany, MD

3, Tom, 10 ABC drivethru, Southbury, CT

];

[Product]:

LOAD * INLINE [

ProdID, Name, Amount

A, Shoe, 1000

B, Bag, 200

];

Thanks,

Dinesh.

Re: Data Type and Data Value

johnw — Fri, 06 May 2011 23:13:24 GMT

Well, it's fairly easy to just concatenate all that together and then sort both fields by load order if you want to see it sorted as indicated. But I don't know what you're trying to accomplish, so that might be a terrible solution for your real requirement.

Data:

LOAD * INLINE [

DataType, Data

Header, 05/30/10

];

CONCATENATE (Data)

LOAD

'Customer' as DataType

,Name & ',' & Address & ',' & City & ',' & State as Data

RESIDENT Customer

;

CONCATENATE (Data)

LOAD

'Product' as DataType

,Name & ',' & Amount as Data

RESIDENT Product

;

CONCATENATE (Data)

LOAD * INLINE [

DataType, Data

Footer, 5

];

Re: Data Type and Data Value

nagaiank — Sat, 07 May 2011 00:16:26 GMT

I had a similar requirement for extracting data from multiple Qlikview tables into one table or csv file and I used the same technique as the one proposed by John. If you will have varying number of rows in Customer and Product table and the if the number in the 'Footer' record should reflect the number of data records accordingly, you will have to tweak loading the 'Footer' record. A sample script is below:

Customer:

LOAD * INLINE [

ID, CustName, Address, City, State

1, Frank, 10 Riverview Drive, Springfield, MO

2, Sue, 21 Middlbury Rd, Parsippany, MD

3, Tom, 10 ABC drivethru, Southbury, CT

];

Product:

NoConcatenate

LOAD * INLINE [

ProdID, Name, Amount

A, Shoe, 1000

B, Bag, 200

];

Data:

LOAD 'Header,30/5/2011' as DataValue

AutoGenerate 1;

Concatenate (Data) LOAD

'Customer' & ',' & CustName & ',' & Address & ',' & City & ',' & State as DataValue

Resident Customer;

Concatenate (Data) LOAD

'Product' & ',' & Name & ',' & Amount as DataValue

Resident Product;

Concatenate (Data) LOAD 'Footer' & ',' & (RowNo()-2) as DataValue

AutoGenerate 1;

Hope this helps, though this not very different from the solution given by John.

Data Type and Data Value

Anonymous — Mon, 09 May 2011 14:18:52 GMT

The intent is to create data feed in a CSV format to a third party. I had thought about concatenation, but decided against it bacause the internal users cannot calidate the CSV data in varying formats and makes sense out of it prior to sending it out. For now, I have created different tables and exported then individually and concatenated in a macro. If anyone knows a better way, I would love to hear that.

Regards,

Dinesh.

Data Type and Data Value

johnw — Mon, 09 May 2011 18:35:11 GMT

You solution sounds good to me, though I've never had that requirement myself, so perhaps there's a better way.