Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Get different dates with different id's

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

miguelbraga

Partner - Specialist III

2017-08-18
05:21 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Get different dates with different id's

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

959 Views

1 Solution

Accepted Solutions

sam_grounds

Contributor III

2017-08-18
05:56 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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(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(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)

)

9 Replies

vinieme12

Champion III

2017-08-18
05:43 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

)

Vineeth Pujari

If a post helps to resolve your issue, please accept it as a Solution.

If a post helps to resolve your issue, please accept it as a Solution.

antoniotiman

Master III

2017-08-18
05:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Miguel,

try this

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

Regards,

Antonio

PrashantSangle

MVP

2017-08-18
05:47 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Great dreamer's dreams never fulfilled, they are always transcended.

Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

sam_grounds

Contributor III

2017-08-18
05:56 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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(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(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)

)

sunny_talwar

MVP

2017-08-18
07:42 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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....

miguelbraga

Partner - Specialist III

2017-08-18
11:19 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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,

MB

See you soon!

726 Views

effinty2112

Master

2017-08-18
05:29 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

miguelbraga

Partner - Specialist III

2017-08-21
04:24 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

726 Views

sam_grounds

Contributor III

2017-08-21
07:08 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

726 Views