topic Re: calculate whether they bought it before in script in QlikView

calculate whether they bought it before in script

uacg0009 — Tue, 25 Feb 2014 02:29:14 GMT

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!

Re: calculate whether they bought it before in script

sujeetsingh — Tue, 25 Feb 2014 03:52:32 GMT

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;

Re: calculate whether they bought it before in script

Tue, 25 Feb 2014 04:10:11 GMT

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.

Re: calculate whether they bought it before in script

Tue, 25 Feb 2014 04:14:09 GMT

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

Re: calculate whether they bought it before in script

uacg0009 — Tue, 25 Feb 2014 04:21:56 GMT

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?

Re: calculate whether they bought it before in script

sujeetsingh — Tue, 25 Feb 2014 04:56:51 GMT

Guzman has done it but i too have updated it

Re: calculate whether they bought it before in script

uacg0009 — Tue, 25 Feb 2014 06:46:49 GMT

Thank you so much, Guzman

Re: calculate whether they bought it before in script

uacg0009 — Tue, 25 Feb 2014 06:47:16 GMT

OK, thank you for your update.

Re: calculate whether they bought it before in script

uacg0009 — Tue, 25 Feb 2014 08:38:03 GMT

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.

Re: calculate whether they bought it before in script

uacg0009 — Tue, 25 Feb 2014 08:41:24 GMT

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!

Re: calculate whether they bought it before in script

Tue, 25 Feb 2014 10:24:39 GMT

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

Re: calculate whether they bought it before in script

uacg0009 — Tue, 25 Feb 2014 11:23:36 GMT

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!

Re: calculate whether they bought it before in script

Tue, 25 Feb 2014 11:58:38 GMT

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.

Re: calculate whether they bought it before in script

uacg0009 — Wed, 26 Feb 2014 09:33:43 GMT

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.

Re: calculate whether they bought it before in script

sujeetsingh — Wed, 26 Feb 2014 10:38:49 GMT

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

Re: calculate whether they bought it before in script

Wed, 26 Feb 2014 16:54:45 GMT

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.