topic Re: Excel sheet Update in QlikView

Excel sheet Update

upaliwije — Thu, 29 Mar 2018 09:21:21 GMT

I want to load data from a excel sheet but it is not in the correct format as shown below. Data relating to members are recorded in two rwos eg Addresss whereas I want them in one row.

CID Member Name Registered date Type Nature Address

000001 Mr. W.M.A. Wijekoon 27/07/2007 001 Personal/Individual Pothuhera.

"Jayasiri"

000002 Mr. J.M.P.B. Jayasundara 27/07/2007 001 Personal/Individual Ahugoda

Priyanthi

000003 Mr. A.M. Mithrathna 27/07/2007 001 Personal/Individual Ahugoda

Hithgahalanda

000004 Mr. A.A. Piyadasa 27/07/2007 001 Personal/Individual Ahugoda

Thimbiriyangaya

000005 Mr. A.M. Heenbanda 27/07/2007 001 Personal/Individual Ahugoda

Can you pls tell me way to insert a column and include 2nd part of address in the same when data is loaded to the QV doc.

Re: Excel sheet Update

olivierrobin — Thu, 29 Mar 2018 09:28:44 GMT

hello

maybe using previous() in the load and testing if CID (for example) is null to populate a field with the value of the 2nd row

i mean

load

if(isnull(CID),previous(CID),CID) as CID,

Member Name,

Registered date,

Type,

Nature,

if(isnull(CID),"",Address) as Address,

if(isnull(CID),Address,'') as Address2

so you have 2 rows per person

and in a 2nd load you group those 2 parts

Re: Excel sheet Update

upaliwije — Thu, 29 Mar 2018 09:44:13 GMT

Thanks,

I get the following msg when I load data

Re: Excel sheet Update

tamilarasu — Thu, 29 Mar 2018 10:08:56 GMT

Could you attach sample file?

Re: Excel sheet Update

upaliwije — Thu, 29 Mar 2018 10:11:00 GMT

Can you please write the script for the grouping part

Re: Excel sheet Update

upaliwije — Thu, 29 Mar 2018 10:13:37 GMT

Kindly look into the attachment and advise me

Re: Excel sheet Update

tamilarasu — Thu, 29 Mar 2018 10:15:13 GMT

There is nothing in the attached file. Could you attach your excel file (MEMBER LIST.xlsx).

Re: Excel sheet Update

upaliwije — Thu, 29 Mar 2018 10:17:35 GMT

Sorry. Pls see the attachment

Re: Excel sheet Update

tamilarasu — Thu, 29 Mar 2018 12:17:41 GMT

Hi,

Sorry for the delay. I had a meeting. Have a look at the attached file.