Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
raju_salmon
Creator II
Creator II

Logic to create new field

Hi Experts,

I have 3 fields as input (first sheet in attached excel), and i need to populate data in new field called "OrderID_Partition" using these three fields.

I have tried using below

Data:

Load

*

from Sample Data;

Load

*,

if(rowno()=1 or TalkID<>previous(TalkID) OR StoreID<>previous(StoreID),1,peek('OrderID_Partition')+1) as OrderID_Partition

Resident Data TalkID, BreakID, StoreID;

But proper result i am not getting... expected result is in sheet 2 in attached excel. Can anyone help me please.

Thanks,

Raju

1 Solution

Accepted Solutions
tracysmart
Creator II
Creator II

Hi Raju

I had a similar requirement across multiple fields and if you want to rank across both your fields, TalkID and StoreID you would enhance Sunnys answer like this

AutoNumber(rowno(),StoreID&'-'&TalkID )as OrderID_Partition

Tracy

View solution in original post

21 Replies
sunny_talwar

Try this:

Data:

LOAD TalkID,

    BreakID,

    StoreID,

    AutoNumber(RowNo(), StoreID) as OrderID_Partition

FROM

[Sample Data.xls]

(biff, embedded labels, table is InPut$);

rupamjyotidas
Specialist
Specialist

Thats Neat..:)

tracysmart
Creator II
Creator II

Hi Raju

I had a similar requirement across multiple fields and if you want to rank across both your fields, TalkID and StoreID you would enhance Sunnys answer like this

AutoNumber(rowno(),StoreID&'-'&TalkID )as OrderID_Partition

Tracy

View solution in original post

sunny_talwar

Thank you

raju_salmon
Creator II
Creator II
Author

Hi Sunny,

Thanks for your time and response. However i am receiving incorrect result if store ID is repeated for other talk ID.

Any suggestion, please find the attachment. Added TalkID 3 information.

Thanks

sunny_talwar

Although it seems that you have got the solution you were looking for, but I am not sure how my solution is any different then your expected solution my friend

Capture.PNG

I find them exactly the same

raju_salmon
Creator II
Creator II
Author

Thanks for quick reply Sunny. Can you please check with this data and suggest.

    

TalkIDBreakIDStoreIDOrderID_Partition
111021
121022
131023
141024
151025
161026
171001
181027
191028
1101029
11110210
1121002
11310211
1141003
2151031
2161032
2171033
2181034
2191035
2201036
2211037
2221038
2231039
22410310
22510311
22610312
22710313
22810314
22910315
23011
23112
311021
321022
331011
341023
351024
361025
371001
381026
391027
3101028
3111029
3121002
31310210
3141003
sunny_talwar

‌Then I would say Tracy's response is the right way to go  

Best,

Sunny

raju_salmon
Creator II
Creator II
Author

This calculation is taking much time as i have 10 million records. My key fields have 8 digits, so would it be better if use autonumber for TALKID and StoreID as separate fields and concatenate?

Data:

Load

autonumber(TalkID)     as TalkID_No

autonumber(SroreID) as SroreID_No

From tab;

Load

*,

AutoNumber(rowno(),TalkID_No&'-'&SroreID_No)as OrderID_Partition1

resident  Data:

Looking for better way.