Deriving recordnum field by using existing fields

[chinnu123]

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

[sunny_talwar]

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

[chinnu123]

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.

[sunny_talwar]

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

[chinnu123]

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.

[sunny_talwar]

Share a sample to show the issue please

[MarcoWedel]

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

[chinnu123]

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.

[sunny_talwar · Accepted solution]

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;

[chinnu123]

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.