Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jumiprado
Creator
Creator

Data model

Hey Guys, i need some help with this model:

I Have four tables with different information, I attached an excel file with an example.

I need to build a model that will allow me to get a

  • Make in the same line graph which can analyze the billing and collection for different MonthYear -> If i have more than one Collection date take the last date and SUM the Collection Money for that COLLECTION_ID

For example:

IDBILL_IDID_BILL_IDBILL_ISSUE_DATEBILL_ISSUE_DATE_IDBILLED_MONEY
10054-22610054-2-2601/12/2014241685100

IDID_BILL_IDBILL_IDCOLLECTION_DATECOLLECTION_DATE_IDCOLLECTED MONEY
10054-210054-2-262601/12/2014241682500
10054-210054-2-262601/01/2015241692600

And take something like this:

IDBILL_IDID_BILL_IDBILL_ISSUE_DATEBILL_ISSUE_DATE_IDBILLED_MONEYCOLLECTION_DATECOLLECTED MONEY
10054-22610054-2-2601/12/201424168510001/01/2015241695100

  • Get For each ID_BILL_ID the collected money. Check that in cases i have more than one collection date for the same ID.
  • I think previously i need to group the information in some tables.
  • Make in the same Bar Chart for each MonthYear where i can count the Suscribes, the Unsuscribes for this MonthYear and the Suspension

-> I tried to make a MasterCalendar For each table but i think thats not the best way.

-> How can i make a good data model for take different indicators, besides those I mention.


Thanks you in advance for your help!



1 Solution

Accepted Solutions
MindaugasBacius
Partner
Partner

Well than instead of ApplyMap use Left join:

LOAD D as ID,

// ApplyMap('map_collected_money', D, ':') as [COLLECTED MONEY],

     BILL_ISSUE_DATE,

     BILL_ISSUE_DATE_ID,

     BILLED_MONEY

FROM

[https://community.qlik.com/thread/226245]

(html, codepage is 1257, embedded labels, table is @4);

left join

LOAD ID,

     sum([COLLECTED MONEY]) as [COLLECTED MONEY],

     max(date(date#(COLLECTION_DATE, 'DD/MM/YYYY'), 'YYYY-MM-DD')) as COLLECTION_DATE

FROM

[https://community.qlik.com/thread/226245]

(html, codepage is 1257, embedded labels, table is @5)

group by ID;

Results:

Screenshot_4.jpg

The attachment also included.

View solution in original post

8 Replies
Not applicable

I realy like concatenate tables, this way i can share dimensions.

FOLOW THE EXAMPLE OF THE MODEL I THINK IT'S REALY GOOD

USE SET ANALISYS FOR YOUR EXPRESSIONS. EXAMPLE : SUM({<FLAG_FATO={"POL"}>} FIELD)

jumiprado
Creator
Creator
Author

Thanks you for your help!

I think this is good, but i need to take something more. How can i create this table from the 2 of the top.

DBILL_ISSUE_DATEBILL_ISSUE_DATE_IDBILLED_MONEY
10054-22610054-2-2601/12/2014241685100

IDID_BILL_IDBILL_IDCOLLECTION_DATECOLLECTION_DATE_IDCOLLECTED MONEY
10054-210054-2-262601/12/2014241682500
10054-210054-2-262601/01/2015241692600

And take something like this:

IDBILL_IDID_BILL_IDBILL_ISSUE_DATEBILL_ISSUE_DATE_IDBILLED_MONEYCOLLECTION_DATECOLLECTED MONEY
10054-22610054-2-2601/12/201424168510001/01/20155100
Not applicable

you can use a dinamic table

MindaugasBacius
Partner
Partner

One solution might be:

map_collected_money:

mapping LOAD ID,

     sum([COLLECTED MONEY])

FROM

[https://community.qlik.com/thread/226245]

(html, codepage is 1257, embedded labels, table is @5)

group by ID;

LOAD D,

  ApplyMap('map_collected_money', D, ':') as [COLLECTED MONEY],

     BILL_ISSUE_DATE,

     BILL_ISSUE_DATE_ID,

     BILLED_MONEY,

     F5,

     F6

FROM

[https://community.qlik.com/thread/226245]

(html, codepage is 1257, embedded labels, table is @4);

The result is:

Screenshot_4.jpg

Find the attachment.

jumiprado
Creator
Creator
Author

Its a good way, if i need the last date collected i have to make a MAX(COLLECTION_DATE) in another mapping load?

Thanks!

MindaugasBacius
Partner
Partner

Well than instead of ApplyMap use Left join:

LOAD D as ID,

// ApplyMap('map_collected_money', D, ':') as [COLLECTED MONEY],

     BILL_ISSUE_DATE,

     BILL_ISSUE_DATE_ID,

     BILLED_MONEY

FROM

[https://community.qlik.com/thread/226245]

(html, codepage is 1257, embedded labels, table is @4);

left join

LOAD ID,

     sum([COLLECTED MONEY]) as [COLLECTED MONEY],

     max(date(date#(COLLECTION_DATE, 'DD/MM/YYYY'), 'YYYY-MM-DD')) as COLLECTION_DATE

FROM

[https://community.qlik.com/thread/226245]

(html, codepage is 1257, embedded labels, table is @5)

group by ID;

Results:

Screenshot_4.jpg

The attachment also included.

View solution in original post

jumiprado
Creator
Creator
Author


Thanks again!
cheburashka
Creator III
Creator III

Hi Juan,

I've created some QvM - Qlikview Modules that I use to help me validate my datamodel.

Maybe you'll find them useful.

,KR Koen

PS: Don't forget to mark answers as helpful and correct.