topic AW:Re: AW:creating a table to simplify many tests in QlikView

creating a table to simplify many tests

Wed, 23 Mar 2011 12:48:12 GMT

Hi, I am having a problem!

I have a database with daily transactions on a customer level. Lets say I have parameters A, B, C, D, E, F and G

Now I am running 10 tests, each test will give me the same A, B, C and D, but new E, F and G.

The result from these tests will be parameters:

E1-E10

F1-F10

G1-G10

My question:

Now I have too many parameters. When I am building Graphs and similar I need to create one graph for each test.

I would like to create some kind of table like this:

Test, E, F, G

1, E1, F1, G1

2, E2, F2, G2

3, E3, F3, G3

and so on.

In other words If I create a graph using the parameter E, I can see different tests just by selecting different Test-values(1-10).

Is this possible, if so, how?

Best Regards

//Hans

AW:creating a table to simplify many tests

brenner_martina — Wed, 23 Mar 2011 13:35:35 GMT

Hi,

you can extract the first character by using the function: Left(Field,1) As Parameter and than you need to load data as a crosstable.

AW:creating a table to simplify many tests

Wed, 23 Mar 2011 14:50:25 GMT

Hi, Thanks for the answer.

I am not sure I understand exactly.

How would the script look like if I have CustomerIDDate as a key between the original database and the 10 tests, and if E, F and G is parameters that will be different for each test.

Best regards

//Hans

AW:Re: AW:creating a table to simplify many tests

brenner_martina — Wed, 23 Mar 2011 16:32:23 GMT

Hi, Hans,

can you upload an example? Thanks

AW:Re: AW:creating a table to simplify many tests

Wed, 23 Mar 2011 19:12:39 GMT

Hi Martina, this is how the script looks today, see below.

What I would like to do is to somehow create a crosstable with all CL, CW and NewValue. If I for example chose Test 36, I want CL=CL36.

Thanks!

//hans

Data:

LOAD date(game_date) as Date,

text(customer_id) as customerID,

text(customer_id) &'|' & date(game_date) as customerIDDate,

division_id,

country_code,

tracking_id,

tracking_channel_nm,

net_win as NetWin,

old_Value as OldValue,

daily_deposits,

daily_net_deposits,

customer_segment_code

FROM

C:\Xxx\2011031012PMNewValue_test32.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Data32_40:

LOAD text(customer_id) &'|' & date(game_date) as customerIDDate,

customer_level as CL32,

customer_weight as CW32,

new_Value as NewValue32

FROM

C:\Xxx\2011031012PMNewValue_test32.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD text(customer_id) &'|' & date(game_date) as customerIDDate,

customer_level as CL33,

customer_weight as CW33,

new_Value as NewValue33

FROM

C:\Xxx\201103102PMNewValue_test33.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD text(customer_id) &'|' & date(game_date) as customerIDDate,

customer_level as CL34,

customer_weight as CW34,

new_Value as NewValue34

FROM

C:\Xxx\201103103PMNewValue_test34.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD text(customer_id) &'|' & date(game_date) as customerIDDate,

customer_level as CL35,

customer_weight as CW35,

new_Value as NewValue35

FROM

C:\Xxx\201103104PMNewValue_test35.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD text(customer_id) &'|' & date(game_date) as customerIDDate,

customer_level as CL36,

customer_weight as CW36,

new_Value as NewValue36

FROM

C:\Xxx\201103105PMNewValue_test36.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD text(customer_id) &'|' & date(game_date) as customerIDDate,

customer_level as CL37,

customer_weight as CW37,

new_Value as NewValue37

FROM

C:\Xxx\20110311NewValue_test37.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD text(customer_id) &'|' & date(game_date) as customerIDDate,

customer_level as CL38,

customer_weight as CW38,

new_Value as NewValue38

FROM

C:\Xxx\20110311NewValue_test38.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD text(customer_id) &'|' & date(game_date) as customerIDDate,

customer_level as CL39,

customer_weight as CW39,

new_Value as NewValue39

FROM

C:\Xxx\201103114PMNewValue_test39.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD text(customer_id) &'|' & date(game_date) as customerIDDate,

customer_level as CL40,

customer_weight as CW40,

new_Value as NewValue40

FROM

C:\Xxx\201103115PMNewValue_test40.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);