Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
if you could post the sample App too that may help me understand the issue
Regards
Alan
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.
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
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.
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;