Data model

[jumiprado]

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:

ID	BILL_ID	ID_BILL_ID	BILL_ISSUE_DATE	BILL_ISSUE_DATE_ID	BILLED_MONEY
10054-2	26	10054-2-26	01/12/2014	24168	5100

ID	ID_BILL_ID	BILL_ID	COLLECTION_DATE	COLLECTION_DATE_ID	COLLECTED MONEY
10054-2	10054-2-26	26	01/12/2014	24168	2500
10054-2	10054-2-26	26	01/01/2015	24169	2600

And take something like this:

ID	BILL_ID	ID_BILL_ID	BILL_ISSUE_DATE	BILL_ISSUE_DATE_ID	BILLED_MONEY	COLLECTION_DATE	COLLECTED MONEY
10054-2	26	10054-2-26	01/12/2014	24168	5100	01/01/2015	24169	5100

• 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!

[#2]

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]

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.

D	BILL_ISSUE_DATE	BILL_ISSUE_DATE_ID	BILLED_MONEY
10054-2	26	10054-2-26	01/12/2014	24168	5100

ID	ID_BILL_ID	BILL_ID	COLLECTION_DATE	COLLECTION_DATE_ID	COLLECTED MONEY
10054-2	10054-2-26	26	01/12/2014	24168	2500
10054-2	10054-2-26	26	01/01/2015	24169	2600

And take something like this:

ID	BILL_ID	ID_BILL_ID	BILL_ISSUE_DATE	BILL_ISSUE_DATE_ID	BILLED_MONEY	COLLECTION_DATE	COLLECTED MONEY
10054-2	26	10054-2-26	01/12/2014	24168	5100	01/01/2015	5100

[#4]

you can use a dinamic table

[MindaugasBacius]

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:

Find the attachment.

[jumiprado]

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 · Accepted solution]

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:

The attachment also included.

[jumiprado]

mindaugasbacius‌ thanks for your help, i have a question about this. When i am going to make a Line Chart with MonthYear with the unique dimension...am i going to have the Collection Money at the MonthYear that the client paid? I need to make this line chart because I have to view the evolution of Billing and Collections!

Thanks again!

[cheburashka]

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.