topic Re: Deriving recordnum field by using existing fields in QlikView

Deriving recordnum field by using existing fields

chinnu123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I am facing a issue in deriving a recordnum field.

For example

I am having a table which have fields

Incident I'd

Type

Month

Status

O/P

Incident I'd. Type. Month. Status. Recno

Inc123. A. Jan. Open. 1

Inc123. B. Jan. Open. 2

Inc123. C. Jan. Open 3

Inc222 A. Jan. Open. 1

Inc333. A Feb. Open. 1

Inc333. B Mar. Open. 2

Inc444. A. Mar. Open. 1

Inc444. B. Apr. Open. 2

Inc444. C. Apr. Open. 3

So by the above example we can see that the incident ids is repeating but type is different for incident I'd s so by using incident I'd and type we need to get record number.

Note: We need to do it in backend only.

Can any one help in this?

Thanks in advance

Thanks,

Chinnu.

Message was edited by: chinnu k

Re: Deriving recordnum field by using existing fields

sunny_talwar — Mon, 31 Jul 2017 19:39:20 GMT

Option 1

Table:

LOAD IncidentID,

Type,

Month,

Status

FROM .....;

FinalTable:

LOAD *,

AutoNumber(RecNo(), IncidentID) as RecNo

Resident Table

Order By IncidentID, Type;

DROP Table Table;

Option 2

Table:

LOAD IncidentID,

Type,

Month,

Status

FROM .....;

FinalTable:

LOAD *,

If(IncidentID = Previous(IncidentID), RangeSum(Peek('RecNo'), 1), 1) as RecNo

Resident Table

Order By IncidentID, Type;

DROP Table Table;

I would suggest using the second method as it will give you better reload time compared to AutoNumber

Re: Deriving recordnum field by using existing fields

chinnu123 — Wed, 02 Aug 2017 18:10:59 GMT

Hi sunny,

Thanks for your quick response

By using above condition I am not getting exact o/p.

Here what I need is if I am selecting A in type the record no 1 must be selected.

In the same way if I am selecting B in type then recno 2 must be selected.

Hope I am clear.

Thanks in advance

Regards,

Chinnu.

Re: Deriving recordnum field by using existing fields

sunny_talwar — Wed, 02 Aug 2017 18:26:25 GMT

This looks exactly as what you wanted, what is wrong here?

Re: Deriving recordnum field by using existing fields

chinnu123 — Wed, 02 Aug 2017 18:43:53 GMT

Hi sunny,

But I am trying to derive some caluculated fields by using record no it showing some error.

I am having a condition in sql like max(decode( recno,1,type)) as test.

max(decode(recno,2,type)) as test1

This condition I need to change it into qlik may be I am missing something here.

Can you please help in this?

Thanks,

Chinnu.

Re: Deriving recordnum field by using existing fields

sunny_talwar — Wed, 02 Aug 2017 18:44:59 GMT

Share a sample to show the issue please

Re: Deriving recordnum field by using existing fields

MarcoWedel — Wed, 02 Aug 2017 22:21:08 GMT

Hi,

it seems like you are trying to use a QlikView field name in an SQL query which won't work.

Besides that another way to generate the Recno field (supposed you want a static relation between Types and Recno) could be:

table1:
LOAD *
INLINE [
    Incident Id, Type, Month, Status
    Inc123, A, Jan, Open
    Inc123, B, Jan, Open
    Inc123, C, Jan, Open
    Inc222, A, Jan, Open
    Inc333, A, Feb, Open
    Inc333, B, Mar, Open
    Inc444, A, Mar, Open
    Inc444, B, Apr, Open
    Inc444, C, Apr, Open
];

Join
LOAD FieldValue('Type',RecNo()) as Type,
    RecNo() as Recno
AutoGenerate FieldValueCount('Type');

hope this helps

regards

Marco

Re: Deriving recordnum field by using existing fields

chinnu123 — Thu, 03 Aug 2017 10:23:38 GMT

Hi Sunny,

Bu using your expression I am getting the output. But the issue is I created some more fields by using Recno.

i.e test1,test2,test3 by using recno and type.

Here When I am clicking on incident id It need to show test1, test2 and ,test3 values in one row instead of that it showing in 3 rows like below image. Can you please help on this?

For your reference I attached sample qvw file

Thanks,

Chinnu.

Re: Deriving recordnum field by using existing fields

sunny_talwar — Thu, 03 Aug 2017 11:11:44 GMT

This?

Table:

LOAD * Inline [

IncidentID, Type, Month, Status

Inc123, A, Jan, Open

Inc123, B, Jan, Open

Inc123, C, Jan, Open

Inc222, A, Jan, Open

Inc333, A, Feb, Open

Inc333, B, Mar, Open

Inc444, A, Mar, Open

Inc444, B, Apr, Open

Inc444, C, Apr, Open

];

FinalTable:

LOAD *,

If(IncidentID = Previous(IncidentID), RangeSum(Peek('RecNo'),1),1) as RecNo

Resident Table

Order By IncidentID, Type;

DROP Table Table;

LinkTable:

LOAD IncidentID,

Only(If((RecNo)=1,Type)) as test1,

Only(If((RecNo)=2,Type)) as test2,

Only(If((RecNo)=3,Type)) as test3

Resident FinalTable

Group By IncidentID;

Re: Deriving recordnum field by using existing fields

chinnu123 — Thu, 03 Aug 2017 12:12:16 GMT

Hi Sunny,

Thanks for your reply,

Is there any chance that we can do it in single table. I don't want two tables in data model.

Thanks,

Chinnu.

Re: Deriving recordnum field by using existing fields

sunny_talwar — Thu, 03 Aug 2017 12:13:48 GMT

Use Left Join...

Left Join (FinalTable)

LOAD IncidentID,

Only(If((RecNo)=1,Type)) as test1,

Only(If((RecNo)=2,Type)) as test2,

Only(If((RecNo)=3,Type)) as test3

Resident FinalTable

Group By IncidentID;

Re: Deriving recordnum field by using existing fields

chinnu123 — Thu, 03 Aug 2017 12:25:41 GMT

Thanks sunny for your help. Its working fine now.