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!

[sujeetsingh]

See you can add this column flag either in script or in the UI.

I have done it in the script have a look

i have modified your script as  below:

Tab:

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

];

Data:

LOAD

VIP, ITEM, ORDER

,if(ITEM='WATCH',1,0) as FG_WAT

,if(ITEM='WALLET',1,0) as FG_WAL

,if(ITEM='SHOE',1,0) as FG_SHOE

Resident Tab;

DROP Table Tab;

[#3]

Hi Changjun,

Check the application that I attached to see if you need.

I added the following script:

FinalTable:

LOAD *,

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

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

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

Resident TempTable;

TempTable is your current table.

Tell me if you have any question about this.

Regards.

[#4]

I forgot drop the TempTable from the script, add in the end of script, drop table TempTable;

[uacg0009]

Hi sujeetsingh,

thank you for your attached file, but I want to calculate whether they bought before, not this order.

Just when order=2, I want to know whether they bought shoe, wallet, watch before order=2.

So could you have any idea to calculate that?

[sujeetsingh]

Guzman has done it but i too have updated it

[uacg0009]

Thank you so much, Guzman

[uacg0009]

OK, thank you for your update.

[uacg0009]

Hi Guzman,

I find a problem when I use the script what you gave me.

Because in my case, there is no this situation, but I think it will happen.

An example like this when VIP = E,

you will see that if someone bought 2 items in one order, it will be wrong when we use peek,

so how to solve this problem?

thank you.

[uacg0009]

Hi sujeetsingh,

I found a problem with the solution, could you please help me to solve that?

the problem is like above I replied to Guzman,

when the VIP E bought 2 things in a order, the peek can not make the flag be correct.

do you have any idea to solve this?

thank you so much!