topic Re: Get different dates with different id's in QlikView

Get different dates with different id's

miguelbraga — Wed, 25 Nov 2020 16:16:04 GMT

Good morning dear experts,

It's been a long time I'm posting a new thread on the community, but I'm with a complicated problem and need help from you experts:

Gysbert Wassenaar, Sunny Talwar, Stefan Wühl, Robert Mika, Henric Cronström, Robert Mika and Rob Wunderlich.

I'll explain my issue here:

I have a table with the following fields:

[Lines] which specify the type of data that I'm evaluating;

[Client Id] which have specific number of each c;

[Date] which specify a Date where the record have been created;

[Amount] which specify a Amount of Sales made in the Date.

There are 16 types of Lines and each one have it's own specific rule which you can see from the following picture:

The rules of each line is as follows:

For Lines [L1, L4, L5, L8, L9, L10, L15, L16] there is a simple Sum(Amount) expression

My problem is to calculate for Lines [L2, L3, L6, L7, L11, L12, L13 and L14] with the following rules

For Line [L2] I need to sum the Amount of the dates that only appears in Lines L2 and not in Lines L3
- Note: there are dates that are the same from Lines L2 and Lines L3
For Line [L3] I need to sum the Amount of the dates that only appears in Lines L3 and not in Lines L2

For Line [L6] I need to sum the Amount of the dates that only appears in Lines L6 and not in Lines L7
- Note: there are dates that are the same from Lines L6 and Lines L7
For Line [L7] I need to sum the Amount of the dates that only appears in Lines L7 and not in Lines L6

For Line [L11] I need to sum the Amount of the dates that only appears in Lines L11 and not in Lines L12
- Note: there are dates that are the same from Lines L11 and Lines L12
For Line [L12] I need to sum the Amount of the dates that only appears in Lines L12 and not in Lines L11

For Line [L13] I need to sum the Amount of the dates that only appears in Lines L13 and not in Lines L14
- Note: there are dates that are the same from Lines L13 and Lines L14
For Line [L14] I need to sum the Amount of the dates that only appears in Lines L14 and not in Lines L13

How can I achieve this in a expression (via a Table chart) and/or in the script (excluding the dates for the Lines [L2, L3, L6, L7, L11, L12, L13 and L14])? I will provide a example of Lines L2 and L3 so if you could help me with that rule I can do the same for the rules of the pairs [L6,L7], [L11, L12] and [L13,L14]. Can you help me?

Best regards and a great weekend,

Data Architect MB

Re: Get different dates with different id's

vinieme12 — Fri, 18 Aug 2017 09:43:25 GMT

try as below

LOAD Lines,

[Client Id],

Date,

Lines&'_'&NUM(Date) as Ln_dt_Key,

Amount

FROM

(biff, embedded labels, table is Sheet1$);

Expression

=Pick(PurgeChar(Lines,'L'),

0, //expression L1 if any!

Sum({<Ln_dt_Key={'L2*'}>}Amount) //expression for L2

Sum({<Ln_dt_Key={'L3*'}>}Amount) //expression for L3

)

Re: Get different dates with different id's

antoniotiman — Fri, 18 Aug 2017 09:46:18 GMT

Hi Miguel,

try this

Sum({<Date=P({<Lines={'L2'}>})-P({<Lines={'L3'}>})>} Amount)

Regards,

Antonio

Re: Get different dates with different id's

PrashantSangle — Fri, 18 Aug 2017 09:47:17 GMT

Hi,

try with indirect set analysis

For Line L2

sum({<Date=e({<Lines={"L3"}>}Date)>}Amount)

For L3 Sales

sum({<Date=e({<Lines={"L2"}>}Date)>}Amount)

Regards,

Prashant Sangle

Re: Get different dates with different id's

sam_grounds — Fri, 18 Aug 2017 09:56:54 GMT

Hi Miguel,

If I may, I'll throw my two cents in. I'm sure such a knowledgeable group of people could put together a better solution, but here is how I would tackle it. I haven't tested it and wrote it outside QlikView, So I'll apologise if it errors.

=pick(match(Lines,'L1','L2','L3','L4','L5','L6','L7','L8','L9','L10','L11','L12','L13','L14','L15','L16'),

sum(Amount),

sum(total{$<Date=P({$<Lines={'L2'}>})>-$<Date=P({$<Lines={'L3'}>})>}Amount),

sum(total{$<Date=P({$<Lines={'L3'}>})>-$<Date=P({$<Lines={'L2'}>})>}Amount),

sum(Amount),

sum(total{$<Date=P({$<Lines={'L6'}>})>-$<Date=P({$<Lines={'L7'}>})>}Amount),

sum(total{$<Date=P({$<Lines={'L7'}>})>-$<Date=P({$<Lines={'L6'}>})>}Amount),

sum(Amount),

sum(total{$<Date=P({$<Lines={'L11'}>})>-$<Date=P({$<Lines={'L12'}>})>}Amount),

sum(total{$<Date=P({$<Lines={'L12'}>})>-$<Date=P({$<Lines={'L11'}>})>}Amount),

sum(total{$<Date=P({$<Lines={'L13'}>})>-$<Date=P({$<Lines={'L14'}>})>}Amount),

sum(total{$<Date=P({$<Lines={'L14'}>})>-$<Date=P({$<Lines={'L13'}>})>}Amount),

sum(Amount),

sum(Amount)

)

Re: Get different dates with different id's

sunny_talwar — Fri, 18 Aug 2017 11:42:06 GMT

I wonder if this is across all the clients or are you talking about individual clients? The problem is that if this is for each client, then you have not provided two different line types for a single client in your data....

Re: Get different dates with different id's

miguelbraga — Fri, 18 Aug 2017 15:19:46 GMT

Hey there experts,

I'll try any sugestions that you guys have told me. When I'm done, I'll consider the best option as the correct answer.

Many thanks,

See you soon!

Re: Get different dates with different id's

effinty2112 — Fri, 18 Aug 2017 21:29:29 GMT

Hi Miguel,

I have the same question as Sunny, nevertheless try :

MapLines:

Mapping

LOAD * INLINE [

F1, F2

L1, L1

L2, L2L3

L3, L2L3

L4, L4

L5, L5

L6, L6L7

L7, L6L7

L8, L8

L9, L9

L10, L10

L11, L11L12

L12, L11L12

L13, L13L14

L14, L13L14

L15, L15

L16, L16

];

Data:

LOAD

ApplyMap('MapLines',Lines) as MapLines,

LOAD Lines,

[Client Id],

Date,

Amount

FROM

Example.xls

(biff, embedded labels, table is Sheet1$);

JoinTable:

LOAD

Distinct

Date,

MapLines,

//[Client Id],

1 as Flag

Resident Data

Where Lines = MapLines;

LOAD

Date,

MapLines,

//[Client Id],

if(Count(DISTINCT Lines)=1,1,0) as Flag

Resident Data

Where Lines <> MapLines

Group by Date, MapLines

//,[Client Id]

;

Left Join(Data) LOAD * Resident JoinTable;

DROP Table JoinTable;

There are lines with the Client Id field commented out. Whether these remain commented out depends on the answer to the question posed by Sunny.

The required expression in your front end will be sum(Flag*Amount).

Cheers

Andrew

Re: Get different dates with different id's

miguelbraga — Mon, 21 Aug 2017 08:24:01 GMT

Thank you Sam Grounds,

This solution is the best and most suitable for me and works just like a charm without the need to change the script.

Regards,

Data Architect MB

Re: Get different dates with different id's

sam_grounds — Mon, 21 Aug 2017 11:08:07 GMT

You're very welcome! I'm glad it was helpful.