calculate whether they bought it before in script

[uacg0009]

Hi all,

I have a problem with the calculation. I want to do it in script and I have some data like:

And I just want to create 3 columns -- FG_WAT, FG_SHO, FG_WAL in every row to show whether they bought watch or shoe or wallet

before this order.

for example,

in the first row, the 3 flags must be 0 or didn't buy, because when order=1 ,there is no previous result.

in the second row, the 3 flags need to be FG_WAT=1, FG_SHOE=0, FG_WAL=0.

similarly, in the third row, the 3 flags need to be FG=WAT=1, FG_SHOW=1, FG_WAL=0.

I hope you know what I want.

Thank you so much!

[#2]

Hi,

In your first example we can identify the real order for the orders table though the field order. Now we can have More of one item per each order. How now can we identify what item must be take before than other to calculate the flag? The order of the table at the reload or with other some rule?

I thinking perhaps if two items are ordered in same order number then in this both items flags we dont should count each and in this case we must count in the order 2 for vip e count the two items ordered previously.

What business rules we must follow?

Tell me if you have any question or if i must explain this.

Regards

[uacg0009]

Hi Guzman,

Sorry because maybe I haven't clarified what I want.

And actually the order means purchase order or ,maybe I need to say, purchase sequence I think.

If there is 2 items in one order, that means ,for example , VIP E, he bought 2 items in one invoice.

he bought SHOE and WALLET in the same time,  so the orders are all 1.

When we calculate the flag, the before bought means all ORDERs < this row's ORDER.

Sorry for my poor English, let me give an example, just the VIP E.

Because in the ORDER = 1 , the E bought SHOE and WALLET.

So when we calculate the flags of ORDER = 2, we need to consider the two items.

So the result what I want is

VIP   ITEM   ORDER   FG_WAT   FG_SHOE   FG_WAL

E    WATCH      2              0                1               1

Do we need to add 3 flags to note what the vip bought in the same invoice? (Because the data just have 3 items)

Or some better idea?

I hope I have clarified what I want and thank you so much!

[#4 · Accepted solution]

Hi,

Yes I think that I understood to you.

Try with this code:

TempTable:

LOAD * INLINE [

    VIP, ITEM, ORDER

    A, WATCH, 1

    A, SHOE, 2

    A, SHOE, 3

    A, WALLET, 4

    B, SHOE, 1

    B, WATCH, 2

    B, WATCH, 3

    C, WATCH, 1

    C, WATCH, 2

    C, SHOE, 3

    C, WALLET, 4

    C, WALLET, 5

    D, WATCH, 1

    D, SHOE, 2

    D, WATCH, 2

    E, SHOE, 1

    E, WALLET, 1

    E, WATCH, 2

];

//The TempTable must be order by VIP ID and Order ID

TempTable2:

LOAD *,

  if(VIP <> Peek(VIP), 0, if(ORDER = Peek(ORDER), Peek(FG_WAT_TMP), if(Peek(ITEM) = 'WATCH', 1, peek(FG_WAT)))) as FG_WAT,

  if(VIP <> Peek(VIP), 0, if(ORDER = Peek(ORDER), Peek(FG_SHOE_TMP), if(Peek(ITEM) = 'SHOE', 1, peek(FG_SHOE)))) as FG_SHOE,

  if(VIP <> Peek(VIP), 0, if(ORDER = Peek(ORDER), Peek(FG_WAL_TMP), if(Peek(ITEM) = 'WALLET', 1, peek(FG_WAL)))) as FG_WAL,

  if(VIP <> Peek(VIP), 0, if(ORDER = Peek(ORDER) and Peek(ITEM) = 'WATCH', 1, peek(FG_WAT_TMP))) as FG_WAT_TMP,

  if(VIP <> Peek(VIP), 0, if(ORDER = Peek(ORDER) and Peek(ITEM) = 'SHOE', 1, peek(FG_SHOE_TMP))) as FG_SHOE_TMP,

  if(VIP <> Peek(VIP), 0, if(ORDER = Peek(ORDER) and Peek(ITEM) = 'WALLET', 1, peek(FG_WAL_TMP))) as FG_WAL_TMP

Resident TempTable;

DROP Table TempTable;

FinalTable:

LOAD

  VIP,

  ITEM,

  ORDER,

  if(VIP = Peek(VIP) and ORDER = Peek(ORDER), Peek(WAT),  if(FG_WAT - FG_WAT_TMP <> 0, 1, 0)) as WAT,

  if(VIP = Peek(VIP) and ORDER = Peek(ORDER), Peek(SHOE),  if(FG_SHOE - FG_SHOE_TMP <> 0, 1, 0)) as SHOE,

  if(VIP = Peek(VIP) and ORDER = Peek(ORDER), Peek(WAL),  if(FG_WAL - FG_WAL_TMP <> 0, 1, 0)) as WAL

Resident TempTable2;

DROP Table TempTable2;

I attached a qvw.

Tell me if you have questions.

Regards.

[uacg0009]

Hi Guzman,

Sorry for the late reply, because today I was checking the script.

I learnt a lot from your script and ideas, so thank you very very much!!

I modified the script that you gave me and I think I made it better so I just want to share to you.

And you also can check it.

In the FinalTable, I use these formulas to calculate the flags:

	if(VIP = Peek(VIP) and ORDER = Peek(ORDER), Peek(WAT),  if(FG_WAT + FG_WAT_TMP > 0, 1, 0)) as WAT,
	if(VIP = Peek(VIP) and ORDER = Peek(ORDER), Peek(SHOE),  if(FG_SHOE + FG_SHOE_TMP > 0, 1, 0)) as SHOE,
	if(VIP = Peek(VIP) and ORDER = Peek(ORDER), Peek(WAL),  if(FG_WAL + FG_WAL_TMP > 0, 1, 0)) as WAL

use flag add flag > 0, I think it is better. what do you think, Does it may cause something wrong?

Finally, thank you so much!

If you have any further questions pls tell me.

[sujeetsingh]

Zhao,

What do mean by "the peek can not make the flag be correct."?

Actually all the three flags present that the in the previous purchase the ITEM is present then 1 else 0.

How you think that the value 2 or  more can have a role in deciding this .

Can you please explain?

Thanks

[#7]

Hi Changjun,

I'm glad I could help.

About your question, both solutions are similar. Is a mathematical operation and then a simple boolean question.

Level of performance and code, both solutions are almost the same.

Tell me if you have any other question.

Regards.