topic Re: Data Load in QlikView

Data Load

Tue, 08 Mar 2016 05:55:48 GMT

Hi All,

I have data something like below

Order Number	Header_text	LineNo
16-101003	Inclusive of VAT@ 14.5%, Loading, Unloading and Transportation Charges	04
16-101003	as actulas	04
16-101003	Immediately within next working day after receiving the Purchase Order	05
16-101007	At the time of unloading the material ,sufficient time to be given to our store persons for the	06
16-101007	Quality & Quantity inspection, acceptance & unloading.	06
16-101003	Goods to be supplied against this order must be properly packed to avoid damage during Transit / Storage.	07
16-101003	Warranty valid for a period of 12 months from the date of supply.	08
16-101003	shall be done in presence of our company representative at your stock yard	10
16-101003	To be Obtained by the Supplier at not extra Cost.	13
16-101003	Any non-tendered materials shall not be supplied until the rates & sizes are approved in writing by the undersigned.	14
16-101003	Material should be supplied between 8:00 am to 6:00 pm on working days (Monday to Saturday).	16
16-101003	60 Days credit from the date of Delivery	90
16-101004	Inclusive of VAT@ 14.5%, Loading, Unloading and Transportation Charges	04
16-101004	as actulas	04

While Loading the data I need to Combine the same LineNo.

Example: In the First and second Order LineNo is 4 So the Output will be.

Order Number Header_text LineNo

16-101003 Inclusive of VAT@ 14.5%, Loading, Unloading and Transportation Charges as actuals 04

Please help me with this.

Regards,

Keerthi KS

Re: Data Load

tresB — Tue, 08 Mar 2016 06:00:52 GMT

Try like:

Load

[Order Number],

Concat( Header_text, ' ') as Header_text,

LineNo

From <> Group By LineNo, [Order Number];

Re: Data Load

Tue, 08 Mar 2016 06:14:17 GMT

It gives Error. Below is my Script

TEPurchaseHeader:

LOAD

Uniqueid as PurchaseHeader_ID,

Order_Number,

Concat(Header_text,' ') as Header_text,

LineNo

FROM

[..\10_data_sources\PurchaseHeader.qvd]

(qvd) Group By Order_Number,LineNo ;

Re: Data Load

tresB — Tue, 08 Mar 2016 06:19:38 GMT

Include the third field as well in the group by clause like:

...Group By Order_Number,LineNo, Uniqueid ;

Re: Data Load

Tue, 08 Mar 2016 06:24:54 GMT

It shows invalid expression. Is there any mistake in Concat

Re: Data Load

tresB — Tue, 08 Mar 2016 06:30:23 GMT

I don't see anything such. Could you post a sample?

Re: Data Load

jagan — Tue, 08 Mar 2016 06:31:28 GMT

HI,

Try like this

TEPurchaseHeader:

LOAD

Uniqueid as PurchaseHeader_ID,

Order_Number,

Concat(Header_text,' ') as Header_text,

LineNo

FROM

[..\10_data_sources\PurchaseHeader.qvd]

(qvd) Group By Order_Number,LineNo, Uniqueid ;

You have to use all the fields used in Load in Group by.

Re: Data Load

pooja_sn — Tue, 08 Mar 2016 06:39:55 GMT

ResultantTable:

Load
Concat( Header_text, ' ') as Header_text,
[Order Number],
LineNo
resident InputTable
Group By LineNo, [Order Number] ;

DROP table InputTable;

Re: Data Load

Tue, 08 Mar 2016 06:42:35 GMT

Sorry it was my mistake in the script. Its Working

Re: Data Load

Tue, 08 Mar 2016 08:46:06 GMT

Can we concatenate based on Uniqueid Order ?

Uniqueid	Order_Number	Header_text	LineNo
1	15M-100895	1) Please issue the shop drawing to our site team and get the approval	02
2	15M-100895	prior to work.	02
3	15M-100895	2) Conduite the cables as per the shop drawing issued by you.	02
4	15M-100895	3) Supply and installation of equipments in good condition.	02
5	15M-100895	Others	02

Concatenate should be on the above order(Uniqueid).

Regards,

Keerthi KS

Re: Data Load

tresB — Tue, 08 Mar 2016 09:00:29 GMT

I guess you need something like:

Table:

Load

From <> ;

Join

Load

Concat( Header_text, ' ') as Header_text,

LineNo

From <> Group By LineNo;

Re: Data Load

Tue, 08 Mar 2016 09:20:23 GMT

The result should be like below

1) Please issue the shop drawing to our site team and get the approval prior to work. 2) Conduite the cables as per the shop drawing issued by you. 3) Supply and installation of equipments in good condition. Others

Re: Data Load

tresB — Tue, 08 Mar 2016 09:23:07 GMT

But your 'prior to work' comes under id '2': ?

2 15M-100895 prior to work.

Re: Data Load

Tue, 08 Mar 2016 09:32:08 GMT

Yes its in Id 2.

It should concatenate in the Uniqueid Order

Re: Data Load

tresB — Tue, 08 Mar 2016 09:34:37 GMT

You mean, every two consecutive rows should be concatenated?

Re: Data Load

Tue, 08 Mar 2016 09:37:51 GMT

No. It should concatenate in the Uniqueid Order.

This is the data.

Uniqueid	Order_Number	Header_text	LineNo
1	15M-100895	Please issue the shop drawing to our site team and get the approval	02
2	15M-100895	prior to work.	02
3	15M-100895	Conduite the cables as per the shop drawing issued by you.	02
4	15M-100895	Supply and installation of equipments in good condition.	02
5	15M-100895	Others	02

Output should be

Please issue the shop drawing to our site team and get the approval prior to work. Conduite the cables as per the shop drawing issued by you. Supply and installation of equipments in good condition. Others'

Complete Header_Text concatenated as one record

Hope its clear.

Re: Data Load

tresB — Tue, 08 Mar 2016 09:52:52 GMT

May be a Order By is needed, like:

Table:

Load

From <> ;

Join

Load

Concat( Header_text, ' ') as Header_text,

LineNo

From <> Group By LineNo Order By Uniqueid;