topic Re: FieldValue in QlikView

FieldValue

Chanty4u — Thu, 13 Oct 2016 12:44:01 GMT

Hi all,

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

Re: FieldValue

sunny_talwar — Thu, 13 Oct 2016 13:04:22 GMT

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;

Re: FieldValue

effinty2112 — Thu, 13 Oct 2016 13:06:11 GMT

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

Re: FieldValue

sunny_talwar — Thu, 13 Oct 2016 15:16:07 GMT

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;

Re: FieldValue

Chanty4u — Thu, 13 Oct 2016 16:49:21 GMT

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

Re: FieldValue

Chanty4u — Thu, 13 Oct 2016 16:51:18 GMT

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

Re: FieldValue

sunny_talwar — Thu, 13 Oct 2016 16:51:30 GMT

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

Re: FieldValue

Chanty4u — Thu, 13 Oct 2016 17:09:01 GMT

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

Re: FieldValue

sunny_talwar — Thu, 13 Oct 2016 17:16:59 GMT

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

Re: FieldValue

Chanty4u — Thu, 13 Oct 2016 17:29:59 GMT

Oh my bad thanks Bro. i never noticed that.

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

Re: FieldValue

sunny_talwar — Thu, 13 Oct 2016 17:31:20 GMT

Create a RowNo() field to show distinct values. or if you add your quantity field which made it distinct, the row would show up

Re: FieldValue

sunny_talwar — Thu, 13 Oct 2016 17:33:33 GMT

Here you are

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;

FinalTable:

LOAD RowNo() as Key,

Resident Table;

DROP Table Table;

Re: FieldValue

Chanty4u — Thu, 13 Oct 2016 17:43:06 GMT

thank you sunny its clear now. tommrow i will check with original data and will close

thanks

Re: FieldValue

sunny_talwar — Thu, 13 Oct 2016 17:43:53 GMT

Sounds good

Re: FieldValue

MarcoWedel — Fri, 14 Oct 2016 23:24:08 GMT

Hi,

maybe the File Wizard Transformation function might help as well:

table1:
LOAD RecNo() as ID,
    Date(Date) as Date,
    Product,
    Type,
    ProNo.,
    [Prod Ref.],
    Productname
FROM [https://community.qlik.com/servlet/JiveServlet/download/1141516-249521/SAMPLE-JEW.xlsx] (ooxml, embedded labels, table is Original, filters(
ColXtr(2, RowCnd(CellValue, 1, StrCnd(null)), 0),
ColXtr(2, RowCnd(CellValue, 1, StrCnd(null, not)), 0),
Remove(Col, Pos(Top, 2)),
Top(5, 'Productname'),
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null)),
Replace(3, top, StrCnd(null)),
Replace(4, top, StrCnd(null)),
Replace(6, top, StrCnd(null)),
Remove(Row, RowCnd(CellValue, 5, StrCnd(null)))
));

(besides the erroneously filled ProNo. and Prod Ref. columns in this example ... )

regards

Marco

Re: FieldValue

Chanty4u — Sat, 15 Oct 2016 04:44:38 GMT

Excellent Macro Nice

Re: FieldValue

MarcoWedel — Sun, 16 Oct 2016 20:27:45 GMT

glad you liked it.

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco