topic Re: If Statement, Mapping Load or other method to handle this use case in QlikView

If Statement, Mapping Load or other method to handle this use case

Tue, 10 Nov 2015 21:14:26 GMT

I have Field XYZ with Values A, B, C, D. This field is used as a key List Box on an existing app.

I need to keep this field and add a new field that groups these values. So:

Field TUV with Values A, B AS Test1 and Values C, D as Test2

** so if I select Test1, then values from A and B will return and if I select Test2, values from C and D will return

What is the best way to do this, either by leveraging an 'If' statement or mapping load or another method.

I would greatly appreciate exact syntax leveraging my mock example. Thanks all!

Re: If Statement, Mapping Load or other method to handle this use case

sunny_talwar — Tue, 10 Nov 2015 21:55:43 GMT

May be this:

Method1:

Mapping_Table:

Mapping

LOAD * Inline [

Field1, Field2

A, Test1

B, Test1

C, Test2

D, Test2

];

Fact:

LOAD [Field XYZ],

ApplyMap('Mapping_Table', [Field XYZ]) as [Grouped Field]

FROM Source;

Method2:

Fact:

LOAD [Field XYZ],

If(Match([Field XYZ], 'A', 'B'), 'Test1', 'Test2') as [Grouped Field]

FROM Source;

Re: If Statement, Mapping Load or other method to handle this use case

maxgro — Tue, 10 Nov 2015 21:57:52 GMT

if you only have few values (4 in your question, A B C D) you can use an if or a pick(match(...)...)

Load

pick(match(XYZ, 'A', 'B', 'C', 'D'), 'Test1', 'Test1', 'Test2', 'Test2') as TUV;

Load

XYZ

....

Re: If Statement, Mapping Load or other method to handle this use case

Wed, 11 Nov 2015 15:25:27 GMT

Thanks for the quick reply. I have tried this method and my script reloads successfully, the new field is added, however, no values are present to select (was hoping for two values: StaffAug and Project. My exact logic is below (minus the logic after this piece to load everything else). Any initial thoughts of what I might have done wrong? note: I am working from an existing, very over engineered script so the root cause could be deeper.

Load *,

pick(match([Financial Tracking Level], 'A', 'AB', 'ABFT', 'ABC'),

'StaffAug', 'StaffAug', 'Project', 'Project') AS [Project Type];

Re: If Statement, Mapping Load or other method to handle this use case

sunny_talwar — Wed, 11 Nov 2015 16:37:57 GMT

Script looks fine to me. I think it might be another issue like you said.

Re: If Statement, Mapping Load or other method to handle this use case

maxgro — Wed, 11 Nov 2015 16:43:00 GMT

with your data

Load *,

pick(match([Financial Tracking Level], 'A', 'AB', 'ABFT', 'ABC'),

'StaffAug', 'StaffAug', 'Project', 'Project') AS [Project Type];

Load * inline [

Financial Tracking Level

ABFT

ABC

ZZZ1

ZZZ2

];

Re: If Statement, Mapping Load or other method to handle this use case

Wed, 11 Nov 2015 20:06:19 GMT

Hi - I just wanted to let you know that the original syntax you provided did end up working -- I will store this for future reference! My values were actually 1. A, 2. AB, 3. ABFT, 4. ABC, so I just needed to adjust those values in the script. Thanks again for your help. I am working a large project so I might have additional questions if I get stuck, but thanks so much.