Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a data like below attached Excel with input and output required. i need to get one extr column
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;
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
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;
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
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
Where are these different values in your database? Can you share?
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
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
Oh my bad thanks Bro. i never noticed that.
But if i want to show in front end is there any option?