FieldValue

[Chanty4u]

Hi all,

I have a data like below attached Excel  with input and  output required.  i need to get one extr column

[sunny_talwar]

You can try this:

Table:

LOAD RowNo() as RowNum,

  Date,

    Product,

    Type,

    ProNo.,

    [Prod Ref.]

FROM

[SAMPLE-JEW.xlsx]

(ooxml, embedded labels, table is Original)

Where Len(Trim(Date)) > 0;

Left Join(Table)

LOAD RowNo() as RowNum,

    Trim(Product) as Productname

FROM

[SAMPLE-JEW.xlsx]

(ooxml, embedded labels, table is Original)

Where Len(Trim(Date)) = 0;

[effinty2112]

Hi Suresh,

                         This script:

Data:

LOAD

RecNo() as Row,

*;

LOAD Date,

     Product,

     Type,

     ProNo.,

     [Prod Ref.]

FROM

[SAMPLE-JEW.xlsx]

(ooxml, embedded labels, table is Original);

ReverseData:

LOAD

Row,

Date,

     Product,

     Type,

     ProNo.,

     [Prod Ref.],

     if(IsNum(Date), Peek(Product)) as Productname

  Resident Data Order by Row desc;

  Drop Table Data;

  Data:

  LOAD

Date,

     Product,

     Type,

     ProNo.,

     [Prod Ref.],

Productname

Resident ReverseData Where IsNum(Date) Order by Row;

drop Table ReverseData;

returns this:

Date	Prod Ref.	Product	ProNo.	Type	Productname
01/04/2016	SA-S/16-17/0001	Trading Limited(Ttl)	MS/16-17/04/001	Sales	KK COLOUR
01/04/2016	SA-S/16-17/002	Trading Limited(Ttl)	MS/16-17/04/002	Sales	KK COLOUR
01/04/2016	SSL/16-17/001	As India Ltd	XXX/2016/04/003	Sales	GG COLOR
01/04/2016	SSL/16-17/002	As India Ltd	XXX/2016/04/004	Sales	GG COLOR
01/04/2016	SSL/16-17/003	As India Ltd	XXX/2016/04/005	Sales	GG COLOR
02/04/2016		Si Limited(Sgsl)		Sales	GK COLOUR
02/04/2016		Si Limited(Sgsl)		Sales	KK COLOUR
03/04/2016	SA-S/16-17/0003	Light Ltd	MS/16-17/04/003	Sales	GK COLOUR
15/06/2016	C16-17/1	GGLtd(GGL)	SSP/2016/06/035	Sales	petrol PD022
15/06/2016	CSHYRS/16-17/132	ght Ltd	SSSS/16-17/06/024	Sales	PP petrol HHHH 16-17

Hope this helps

Cheers

Andrew

[sunny_talwar]

I think I did not do it right the first time, seems you need this:

Table:

LOAD RowNo() as RowNum,

  Date,

    Product,

    Type,

    ProNo.,

    [Prod Ref.]

FROM

[SAMPLE-JEW.xlsx]

(ooxml, embedded labels, table is Original)

Where Len(Trim(Date)) > 0;

TempTable:

LOAD If(Len(Trim(Date)) = 0, Peek('RowNum'), RangeSum(Peek('RowNum'), 1)) as RowNum,

  Date,

    Trim(Product) as Productname

FROM

[SAMPLE-JEW.xlsx]

(ooxml, embedded labels, table is Original);

Left Join (Table)

LOAD RowNum,

  Productname

Resident TempTable

Where Len(Trim(Date)) = 0;

DROP Table TempTable;

[Chanty4u]

Hi stalwar1

thanks for your prompt response .

Sorry for late reply. I hope everything is fine.  But one thing is missing in my original data.

in my data below

having same productname with different values are there  above your code working but  only one value is showing .  i need two productnames with different values

[Chanty4u]

Hi Andy,

Thanks its working fine.  but it is giving  Distinct productname

As my data having  two same productname with different values?

how can i achieve this?

Best

Suresh

[sunny_talwar]

Where are these different values in your database? Can you share?

[Chanty4u]

chk in my sample data also  chk this

for  yellow field values  are same    so it is taking as  single row only.

now  i want    3productnames as 3rows

[sunny_talwar]

Brother it is a table box object and table box object only show distinct combinations of all the fields.

Look here in the table viewer

[Chanty4u]

Oh my bad thanks Bro.  i never  noticed that.

But if i want to show in front end is there any option?