topic Re: Logic to create new field in QlikView

Logic to create new field

raju_salmon — Tue, 13 Sep 2016 13:17:18 GMT

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

Re: Logic to create new field

sunny_talwar — Tue, 13 Sep 2016 13:58:24 GMT

Try this:

Data:

LOAD TalkID,

BreakID,

StoreID,

AutoNumber(RowNo(), StoreID) as OrderID_Partition

FROM

[Sample Data.xls]

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

Re: Logic to create new field

rupamjyotidas — Tue, 13 Sep 2016 14:07:43 GMT

Thats Neat..:)

Re: Logic to create new field

tracysmart — Tue, 13 Sep 2016 14:15:18 GMT

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

Re: Logic to create new field

sunny_talwar — Wed, 14 Sep 2016 02:11:10 GMT

Thank you

Re: Logic to create new field

raju_salmon — Wed, 14 Sep 2016 04:25:22 GMT

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

Re: Logic to create new field

sunny_talwar — Wed, 14 Sep 2016 04:32:55 GMT

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

I find them exactly the same

Re: Logic to create new field

raju_salmon — Wed, 14 Sep 2016 04:35:17 GMT

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

TalkID	BreakID	StoreID	OrderID_Partition
1	1	102	1
1	2	102	2
1	3	102	3
1	4	102	4
1	5	102	5
1	6	102	6
1	7	100	1
1	8	102	7
1	9	102	8
1	10	102	9
1	11	102	10
1	12	100	2
1	13	102	11
1	14	100	3
2	15	103	1
2	16	103	2
2	17	103	3
2	18	103	4
2	19	103	5
2	20	103	6
2	21	103	7
2	22	103	8
2	23	103	9
2	24	103	10
2	25	103	11
2	26	103	12
2	27	103	13
2	28	103	14
2	29	103	15
2	30	1	1
2	31	1	2
3	1	102	1
3	2	102	2
3	3	101	1
3	4	102	3
3	5	102	4
3	6	102	5
3	7	100	1
3	8	102	6
3	9	102	7
3	10	102	8
3	11	102	9
3	12	100	2
3	13	102	10
3	14	100	3

Re: Logic to create new field

sunny_talwar — Wed, 14 Sep 2016 04:39:39 GMT

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

Best,

Sunny

Re: Logic to create new field

raju_salmon — Wed, 14 Sep 2016 05:04:55 GMT

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.

Re: Logic to create new field

sunny_talwar — Wed, 14 Sep 2016 05:14:50 GMT

Are you saying that there is a big difference in reload time between with and without AutoNumber? Can you share the time difference it take to reload 10 million without AutoNumber and with autonumber?

Re: Logic to create new field

raju_salmon — Wed, 14 Sep 2016 05:18:29 GMT

It took 40min to reload 3 lakh records... still reload is going...

For this calculation AutoNumber(RowNo(), StoreID) as OrderID_Partition

taking 20 min to load everything.

Re: Logic to create new field

raju_salmon — Wed, 14 Sep 2016 05:20:42 GMT

No. Time difference is between below lines

AutoNumber(rowno(),StoreID)as OrderID_Partition - Taking minutes

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

Re: Logic to create new field

sunny_talwar — Wed, 14 Sep 2016 05:21:53 GMT

Would you be able to share the script where you are doing this?

Re: Logic to create new field

raju_salmon — Wed, 14 Sep 2016 05:25:47 GMT

First tried in this way

Data:

LOAD TalkID,

BreakID,

StoreID,

AutoNumber(RowNo(), StoreID) as OrderID_Partition,

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

FROM

[Sample Data.xls]

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

Data:

LOAD TalkID,

BreakID,

StoreID,

StoreID&'-'&TalkID as Key

// AutoNumber(RowNo(), StoreID) as OrderID_Partition,

// AutoNumber(rowno(),StoreID&'-'&TalkID )as OrderID_Partition1

FROM

[Sample Data.xls]

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

Load

AutoNumber(rowno(),Key)as OrderID_Partition1

resident Data

Taking hours for both scenarios...

Re: Logic to create new field

sunny_talwar — Wed, 14 Sep 2016 05:30:00 GMT

How about just doing one autonumber at a time? Have you tried this?

Data:

LOAD TalkID,

BreakID,

StoreID,

AutoNumber(RowNo(), StoreID&TalkID) as OrderID_Partition

FROM

[Sample Data.xls]

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

Re: Logic to create new field

raju_salmon — Wed, 14 Sep 2016 05:38:57 GMT

Right now loading with same code.. 4L records loaded for one hour...

Re: Logic to create new field

raju_salmon — Wed, 14 Sep 2016 05:43:42 GMT

Hi Tracy,

Did you try for good performance when you got these kind of requirements? For me it is taking much time to load 10 million records.

Thanks.

Re: Logic to create new field

sunny_talwar — Wed, 14 Sep 2016 05:50:28 GMT

Don't know if there are performance benefits or not, but you can try this:

Table:

LOAD TalkID,

BreakID,

StoreID,

OrderID_Partition as Check

FROM

[https://community.qlik.com/thread/232515]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

If(TalkID = Previous(TalkID) and StoreID = Previous(StoreID), RangeSum(Peek('OrderID_Partition'), 1), 1) as OrderID_Partition

Resident Table

Order By TalkID, StoreID, BreakID;

DROP Table Table;

Re: Logic to create new field

raju_salmon — Wed, 14 Sep 2016 06:05:05 GMT

Thanks a lot for your time Sunny, looks like performance is slightly better... i will look for alternates even..