Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Combining information from previous tables in load script

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)

FieldAFieldB
A1B1
A2B2
A3B3

Table 2 (From File 2)

FieldCFieldDFieldE
C1D1E1
C2D2E2

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

FieldAFieldF
A1C1
A2C1+D1
A3C2+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?

1 Solution

Accepted Solutions
Not applicable

Re: Re: Re: Combining information from previous tables in load script

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.

6 Replies
rustyfishbones
Honored Contributor II

Re: Combining information from previous tables in load script

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
Honored Contributor II

Re: Combining information from previous tables in load script

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

Regards

Alan

Not applicable

Re: Re: Combining information from previous tables in load script

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:

ProductCases
Product11221263.5

Product2

732930.9
Product3489965.6
Product4634
Product535665

I hope that helps.

rustyfishbones
Honored Contributor II

Re: Re: Combining information from previous tables in load script

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

Not applicable

Re: Re: Re: Combining information from previous tables in load script

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.

Not applicable

Re: Re: Re: Re: Combining information from previous tables in load script

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;