Combining information from previous tables in load script

[#1]

Okay, I'm not sure if this is possible.

This is a simplified example of what tables my load script currently produces.

Table 1 (From File 1)

FieldA	FieldB
A1	B1
A2	B2
A3	B3

Table 2 (From File 2)

FieldC	FieldD	FieldE
C1	D1	E1
C2	D2	E2

I would like to keep those tables but add this one as well:

FieldA	FieldF
A1	C1
A2	C1+D1
A3	C2+D2+E2

I'm not sure how to do this or if it's possible.  Here's the load script I was playing with, but I can't figure out what I would put for the FROM section.

Load FieldA,

If(FieldA=A1, C1,

     If(FieldA=A2, C1+D1,

          If(FieldA=A3, C2+D2+E2)

     )

) as FieldF

I can't see concatenate or join working here since the FieldF is so erratic/illogical...  Can you pull from two resident tables or two files?  Or, am I missing the obvious, and there's an easy solution?

[rustyfishbones]

Hi,

I would first try sort the issue with FieldF

Load that table first and try sort out the issue with FieldF by using SUBFIELD()

Something like

LOAD

FieldA,

SUBFIELD(FieldF,',') AS FieldF

FROM wherever.csv

[rustyfishbones]

if you could post the sample App too that may help me understand the issue

Regards

Alan

[#4]

Hi, thanks for taking a look.

I'm not sure what you mean by subfields helping in this case.  However I attached a sample app that I hope will help explain better.

In the attached example, I'm looking to create the following table:

Product	Cases
Product1	1221263.5
Product2	732930.9
Product3	489965.6
Product4	634
Product5	35665

I hope that helps.

[rustyfishbones]

Hi

Each Table in Qlikview can only contain one common field

You have 2 in each table, which is causing the creation of synthetic tables

Try to Concatenate each Table into 1 Table and see what that result brings

[#6 · Accepted solution]

Hi Sophia,

If your goal is to create a table in the application, then try to use ValueList(), Pick(), and Match() function.

Your dimension:

=ValueList('Product1', 'Product2', 'Product3', 'Product4', 'Product5')

Your Expression:

pick(match(ValueList('Product1', 'Product2', 'Product3', 'Product4', 'Product5'), 'Product1', 'Product2', 'Product3', 'Product4', 'Product5'),

sum([Basic Product1 Cases]),

sum([Basic Product2 Cases])+sum([Mid Product2 Cases]),

sum([Basic Product3 Cases])+sum([Mid Product3 Cases])+sum([Premium Product3 Cases]),

sum([Product4 Cases]),

sum([Product5 Cases]))

or

My solution is very crude but it works...

I used your script to create the tables and then concatenated all into one table.

Then Added fields as you described in your application.

  Alt([Basic Product1 Cases], 0) as Product1,

  Alt([Basic Product2 Cases], 0) + Alt([Mid Product2 Cases], 0) as Product2,

  Alt([Basic Product3 Cases], 0) + Alt([Mid Product3 Cases], 0) + Alt([Premium Product3 Cases], 0) as Product3,  

  Alt([Product4 Cases], 0) as Product4,

  Alt([Product5 Cases], 0) as Product5

Then Created a Product Field and Cases using a for loop.

Hopefully this is what you wanted.

[#7]

Thanks for all your help, the idea you uploaded works like a dream!

I wound up creating a key that I added on to the end so I can input the actual product names so people who view the QVW understand it better.  I thought I'd share it in case anyone else can use it.  I added the bits in red.

For i=1 to 5;  

    Support_Cases:

    Load

        'Product$(i)'    as Product_Num,

        Product$(i)        as Cases

    Resident tmp_Support_Cases;

Next

Join Load * Inline [Product_Num, Product

Product1, <REAL PRODUCT1 NAME>

Product2, <REAL PRODUCT2 NAME>

Product3, <REAL PRODUCT3 NAME>

Product4, <REAL PRODUCT4 NAME>

Product5, <REAL PRODUCT5 NAME>

];

Drop Table tmp_Support_Cases;